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..
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.