Getting multiple values between two different characters.

[SpencerC]

Hello all,

I have a list of values that look like this: ;Asphalt Late Model>Rear Suspension;Road Race>Rear Suspension


What I need to do is get all of the values between ; & >.


So in this example the output would be Asphalt Late Model;Road Race


Any help would be very appreciated, as I've been stuck on this one for a while.

 

[ranman256]

paste this macro and run...

Sub aParseData()
Dim vWord, vOut, vClip
Dim iStart As Integer, iEnd As Integer

Range("A2").Select
While ActiveCell.Value <> ""
   vWord = ActiveCell.Value
   iStart = InStr(vWord, ";")
   While iStart > 0
        iEnd = InStr(vWord, ">")
        vClip = Mid(vWord, iStart + 1, iEnd - iStart - 1)
        vOut = vOut & vClip & ";"
        vWord = Mid(vWord, iEnd + 1)
        iStart = InStr(vWord, ";")
   Wend
   ActiveCell.Offset(0, 1).Value = vOut
   vOut = ""
   ActiveCell.Offset(1, 0).Select   'next row in source
Wend
End Sub

 

[István Hirsch]

Try this:

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(";"&$A2,";",">"),">",REPT(" ",200)),200*2*COLUMNS($A:A),200))

 

[SpencerC]

Thank you for this macro, it does parse the data correctly but it is giving me an error "run-time error '5': invalid procedure call or argument" and when I hit debug it is highlighting the vClip line. Any ideas? Thank you for the help!

 

[SpencerC]

Istvan, your formula seems to only parse the first result, instead of all results in the cell. Any ideas? Thank you!

 

[SpencerC]

ranman256, its my bad that I didn't mention it, but the amount of values can vary so I can have cells like:

;Dirt Late Model/Modified>Engine>Other Engine;Drag Race>Engine>Other Engine;Off Road>Engine>Other Engine;Asphalt Late Model>Engine>Other Engine;Road Race>Engine>Other Engine

 

[István Hirsch]

Istvan, your formula seems to only parse the first result, instead of all results in the cell. Any ideas? Thank you!

I forgot to say that you should copy across and down the formula put in B2 to get all results.

 

[lhartono]

Try this,

[COLOR=#0000cd]Sub SplitParse()

Dim str1        As String
Dim n           As Long
Dim arr1
Dim part

str1 = Range("A2").Value2
arr1 = Split(str1, ";")
n = 2
For Each part In arr1
    If InStr(1, part, ">") > 0 Then
        Range("B" & n) = Trim$(Mid(part, 1, InStr(1, part, ">") - 1)) [/COLOR][COLOR=#d3d3d3]'& ";"[/COLOR][COLOR=#0000cd]
        n = n + 1
    End If
Next
Columns("B:B").Columns.AutoFit

End Sub[/COLOR]

 

[István Hirsch]

Try this,

[COLOR=#0000cd]Sub SplitParse()

Dim str1        As String
Dim n           As Long
Dim arr1
Dim part

str1 = Range("A2").Value2
arr1 = Split(str1, ";")
n = 2
For Each part In arr1
    If InStr(1, part, ">") > 0 Then
        Range("B" & n) = Trim$(Mid(part, 1, InStr(1, part, ">") - 1)) [/COLOR][COLOR=#d3d3d3]'& ";"[/COLOR][COLOR=#0000cd]
        n = n + 1
    End If
Next
Columns("B:B").Columns.AutoFit

End Sub[/COLOR]

It extracts the string if there is no semicolon, for example, from "Rear suspension>and others".

 

[István Hirsch]

Try this:

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(";"&$A2,";",">"),">",REPT(" ",200)),200*2*COLUMNS($A:A),200))

Better version (enter into B2, copy across and down):

=MID(SUBSTITUTE(SUBSTITUTE($A2,";","$",COLUMNS($A:A)),">","ß",COLUMNS($A:A)),FIND("$",SUBSTITUTE(SUBSTITUTE($A2,";","$",COLUMNS($A:A)),">","ß",COLUMNS($A:A)))+1,FIND("ß",SUBSTITUTE(SUBSTITUTE($A2,";","$",COLUMNS($A:A)),">","ß",COLUMNS($A:A)))-FIND("$",SUBSTITUTE(SUBSTITUTE($A2,";","$",COLUMNS($A:A)),">","ß",COLUMNS($A:A)))-1)