Split Merged Data from one column in to 3

[Trig79]

Hi

I have a string of data in column A:

0| 20| 266|
6| 21| 180|
0| 28| 180|
0| 17| 294|
0| 11| 240|
0| 8| 221|
4| 27| 215|

Which needs to be split in to 3 columns; B,C,D. The above data example is carbon copy, therefore i am also seeing the '|' after each number, this will need to be removed when the numbers are placed in to in to the 3 columns. The data sheet is large with current around 9000 rows, hence i thought a Macro would be more productive over a formula


welcome any help

thanks

[arlu1201]

Have you tried doing a text to columns split using the delimited option?

[Leonsw]

Try the folllowing:
Column 1: =LEFT(A1,FIND("|",A1)-1)
Column 2:=MID(A1,FIND("|",A1,2)+1,FIND("|",A1,3)-3)
Column 3:=MID(A1,FIND("|",A1,3)+1,LEN(A1)-FIND("|",A1,3)-1)

[arlu1201]

Nice solution Leonsw. I did try it at my end and found extra spaces in the 2nd and 3rd column.

If you do not want to retain these spaces, Trig79, then you can use the trim function before the 2nd and 3rd column functions like this -

Column 2:

Please Login or Register  to view this content.

Repeat the same way for column 3.

[Trig79]

Guy's

it worked perfectly thanks for your help


trig79

[arlu1201]

Ok, great. I will mark this thread as solved for you since you will not be able to do it now (since its been more than 48 hrs since the thread was opened).