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

[rccline]

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

 

[combo]

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"):

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

 

[rccline]

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, " "))

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

 

[rccline]

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

 

[Andrzejek]

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

Dim v
Dim R As Integer
R = 2   [green]'Start in row 2[/green]

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.

 

[rccline]

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

 

[Andrzejek]

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.

 

[rccline]

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.

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

 

[Andrzejek]

You did not answer my questions....

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[pre]
B
1
2 34-20-50
3 34-14-49
4 34-17-49
[/pre]

"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.

 

[rccline]

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

 

[Andrzejek]

First, the star should go to combo (but, thank you)
Second, relative reference - no, columns are 'hard-coded'
[tt]
Cells(R, [blue]1[/blue])[/tt] looks at the data in [blue]first[/blue] column (A)[tt]
Cells(R, [blue]2[/blue]).Value = v[/tt] writes the data into [blue]second[/blue] 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.

 

[rccline]

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

Robert

 

[strongm]

Or just for fun, a regexp function such as:

[blue]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[/blue]

which you can then call on the sheet as

[tt]=ExtractText(A1)[/tt]

 

[rccline]

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