INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Excel: Extract a variable string from string within a Cell

Excel: Extract a variable string from string within a Cell

(OP)
I have a column named "Description." Each row of the column contains a sub-string I need to extract from the string within a cell.
The substring is in the form of three sets of numbers. The first number can be either a one digit or a two digit number; the second and third numbers are always two digit numbers.

To reiterate, the string I wish to extract is written in either of two forms:

36-10-72
7-14-65

This set of three numbers could be placed anywhere within the string of a cell.

DESCRIPTION
34-20-50 PART NW 1/4 NORTH OF ROW
LOT 17 BREWER'S 2ND S/D PART NW 1/4 NW 1/4 34-14-49
TRACTS 1 & 2 NEEL'S 4TH & PLUM S/D IN SE/4SE/4 34-17-49 (6.88 ACRES)

Is it possible to extract this string from the cell of each row?

Thank you

Robert


RE: Excel: Extract a variable string from string within a Cell

Assuming that you've already assigned cell to a range type variable rngCell and the searched string is surrounded by spaces or is at one of edges of whole text (i.e whole word matching, no match in "TRACTS 1 & 2 NEEL'S 4TH & PLUM S/D IN SE/4SE/4 34-17-49(6.88 ACRES"):

CODE -->

Dim v, strFound as string
strFound = "" ' clear variable when looping in column
For Each v In Split(Cstr(rngCell, " "))
    If v Like "##-##-##" Or v Like "#-##-##" then strFound = v
Next v 

combo

RE: Excel: Extract a variable string from string within a Cell

(OP)

Combo:

Thank you very much.

I defined the range as rngCell. When I created the macro to run the code, the code will not compile. I get "Syntax Error" for the following line of code:

For Each v In Split(Cstr(rngCell, " "))

CODE

Sub Macro_ExtractString()
'
' Macro_ExtractString Macro
'
' Keyboard Shortcut: Ctrl+e

Dim v, strFound As String
strFound = "" ' clear variable when looping in column
For Each v In Split(Cstr(rngCell, " "))
    If v Like "##-##-##" Or v Like "#-##-##" Then strFound = v
Next v
'
End Sub 

What is wrong with that line of code?

Thank you again.

Robert

RE: Excel: Extract a variable string from string within a Cell

(OP)
It looks like this macro will just clear the variable from the string. I need to place that variable in another cell in the same row.

Robert

RE: Excel: Extract a variable string from string within a Cell

Using combo's syntax, but a lot less 'elegant' way...
Assuming you have the data in column A, and want to place the ##-##-## in column B

CODE

Dim v
Dim R As Integer
R = 2   'Start in row 2

Do While Cells(R, 1).Value <> ""
    For Each v In Split(Cells(R, 1).Value, " ")
        If v Like "##-##-##" Or v Like "#-##-##" Then
            Cells(R, 2).Value = v
        End If
    Next v
    R = R + 1
Loop 

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Excel: Extract a variable string from string within a Cell

(OP)
Great! Now. The code compiles. When I run there is no visible action.
If I step through the code using F8, I step through the code with no errors, but the cursor never leaves row 2.

Now, could it be because the sequence "##-##-##" is refering to numbers and the digits are actually text?

Thank you.

Robert

RE: Excel: Extract a variable string from string within a Cell

Which code did you use: mine or combo's?
If mine, what column do you have your DESCRIPTION in and which column you want to copy the extracted text into?

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Excel: Extract a variable string from string within a Cell

(OP)
Assuming the code is only looking for numbers, I modified the code substituting ? for #.

No change in the code's behavior. No errors, but no results.

CODE

Sub Macro_ExtractString()
Dim v
Dim R As Integer
R = 2   'Start in row 2

Do While Cells(R, 1).Value <> ""
    For Each v In Split(Cells(R, 1).Value, " ")
        If v Like "??-??-??" Or v Like "?-??-??" Then
            Cells(R, 2).Value = v
        End If
    Next v
    R = R + 1
Loop

End Sub 

RE: Excel: Extract a variable string from string within a Cell

You did not answer my questions.... smile

So I copied your data from your original post:
DESCRIPTION
34-20-50 PART NW 1/4 NORTH OF ROW
LOT 17 BREWER'S 2ND S/D PART NW 1/4 NW 1/4 34-14-49
TRACTS 1 & 2 NEEL'S 4TH & PLUM S/D IN SE/4SE/4 34-17-49 (6.88 ACRES)

and pasted it to column A

Run the code in Macro_ExtractString() sub, and ended up with this in Column B
      B
1
2 34-20-50
3 34-14-49
4 34-17-49
 

"No errors, but no results" - well, I see the results in column B...

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Excel: Extract a variable string from string within a Cell

(OP)
Great! If I move the data to column A, it runs just fine.

THANK YOU.

Interesting... When I created the macro, I set it for "relative reference." Looking at your code, it looks like the code was written for a relative reference.

Am I reading that wrong?

Robert

RE: Excel: Extract a variable string from string within a Cell

First, the star should go to combo (but, thank you)
Second, relative reference - no, columns are 'hard-coded'

Cells(R, 1)
looks at the data in first column (A)
Cells(R, 2).Value = v
writes the data into second column (B)
R is just the counter to increment the row number.

"Great! If I move the data to column A, it runs just fine."
That's why I asked: what column do you have your DESCRIPTION in and which column you want to copy the extracted text into? If you would answer, you wouldn't have to move the data to Col A and have the outcome in ColB. It would be whatever columns you need.

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Excel: Extract a variable string from string within a Cell

(OP)
Yes Andy... thank you again! And Star for Combo as well. I am grateful to you an Combo both!

Robert

RE: Excel: Extract a variable string from string within a Cell

Or just for fun, a regexp function such as:

CODE

Public Function ExtractText(strTest As String, Optional strRegExp As String = "\b\d{1,2}-\d\d-\d\d\b") As String
    With CreateObject("vbscript.regexp")
        .Pattern = strRegExp
        With .Execute(strTest)
            If .Count > 0 Then
                ExtractText = .Item(0)
            End If
        End With
    End With
End Function 

which you can then call on the sheet as

=ExtractText(A1)

RE: Excel: Extract a variable string from string within a Cell

(OP)
That is a great post Strongm! I have never seen anything like that! I can at least... most of the time read some sort of sense in code, but that one threw me.
I had never seen code executed in a formula like that before either. I am in awe.

Thank you!

Robert

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close