Solved: trim function.

[khalid79m]

In range a2 to a and lastrow, I have data which is seperated by "_" for example

a2 = London_Mark_123145
a3 = Manchester_Matthew_123

i want b2 = London
i want c2 = Mark
i want d2 = 123145

i want b3 = Manchester
i want c3 = Matthew
i want d3 = 123

Can anyone help

[Bob Phillips]

Use Data>Text To Columns with a delimiter of _.

[khalid79m]

I need to code it... xld..

[GTO]

I see that you said code, this is just in case a formula would be okay, as well as practice for me. I would expect that at least teh second formula may be less than stellar.

In B2:  =TRIM(LEFT(A2,FIND("_",A2,1)-1))
In C2:  =TRIM(MID(A2,FIND("_",A2,1)+1,FIND("_",A2,FIND("_",A2)+1)-FIND("_",A2,1)-1))
In D2:  =MID(A2,FIND("_",A2,FIND("_",A2)+1)+1,255)

Mark

[Bob Phillips]

Then code the TExt To columns, do it in Excel with the macro recorder on.

[khalid79m]

thanks xld ,, was having a dim moment