Parse string out to different cells

[kwagner1]

Greetings,

I have the following string in cell A2 thru A2000 (6 fields concatenated into 1 cell)
GLB MO;GW TCH;ADV WL MK;CL FIN REL ;CL FIN DIS;CLI FN DS

I want: to use a formula to parse out the 6 fields into seperate cols in excel.
Col B2 = GLB MO
Col C2 = GW TCH
Col D2 = ADV WL
etc....

How can I best obtain the desired result via an Excel formula? (i know i can manually use "text to columns" but i want to avoid the manual steps.)

Thanks!

 

[Richard Schollar]

Hi

Try this in A2 copied across:

=TRIM(LEFT(SUBSTITUTE(REPLACE(";"&$A2,1,FIND("@",SUBSTITUTE(";"&$A2,";","@",COLUMNS($A$2:A$2))),""),";",REPT(" ",100),1),100))

 

[jamieleeuk]

not sure how often changes are made to the worksheet, but you could copy the following inot the "On Change" function, or put it into a command button.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), _
        TrailingMinusNumbers:=True
End Sub

I have noticed that the on change gets annoying, so maybe put it into a command button.

 

[kwagner1]

beautifule thing guys !!! I went with the formula appraoch rather than the code. Thanks !!!!

 

[jamieleeuk]

Not suprised... that forumla is kick ***!