PDA

View Full Version : Solved: trim function.



khalid79m
04-13-2010, 04:01 AM
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
04-13-2010, 04:20 AM
Use Data>Text To Columns with a delimiter of _.

khalid79m
04-13-2010, 05:04 AM
I need to code it... xld..

GTO
04-13-2010, 05:13 AM
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
04-13-2010, 05:16 AM
Then code the TExt To columns, do it in Excel with the macro recorder on.

khalid79m
04-13-2010, 05:30 AM
thanks xld ,, was having a dim moment