Splitting data into columns (2003)

Topic

New Reply

I need to split data into separate columns, using the / symbol as the delimiter. For various reasons, I cannot use “Text To Columns” and was wondering if there was a way to do this using formulas. I know about LEFT, RIGHT, MID etc but in view of the fact that the / symbol could be anywhere in the text, this is not going to work. I am attaching a sample file which shows the type of data I am using. Thanks in advance for any suggestions.

Reply | Quote

Replies

You’ve included the data that you are starting with but give no indication of what you desire for the outcome.

You neglect to specify why “Text to Columns…” is out of the question. I’m assuming that the slashes midway in your data represents a date separator.

Reply | Quote

The first group is easy:
=LEFT(A2,FIND(“/”,A2)-1)

The subsequent ones get more complicated:
Here is the 2nd group (between the 1st and 2nd “/”):
=MID(A2,1+FIND(CHAR(1),SUBSTITUTE(A2,”/”,CHAR(1),1)),FIND(CHAR(1),SUBSTITUTE(A2,”/”,CHAR(1),2))-FIND(CHAR(1),SUBSTITUTE(A2,”/”,CHAR(1),1))-1)

The 3rd group (between the 2nd and 3rd “/”):
=MID(A2,1+FIND(CHAR(1),SUBSTITUTE(A2,”/”,CHAR(1),2)),FIND(CHAR(1),SUBSTITUTE(A2,”/”,CHAR(1),3))-FIND(CHAR(1),SUBSTITUTE(A2,”/”,CHAR(1),2))-1)

Change the values in red for the grouping. The first and last is the occurence of the “/” to start and the middle one is the “/” to goto

Steve

Reply | Quote

Splitting data between “/”

Try….
1] A2 : AO/USD/TEST/20/06/09/FRN

2] B2 enter the formula, and copied across to H2

=TRIM(MID(SUBSTITUTE($A2,”/”,REPT(” “,50)),COLUMN(A1)*50-49,50))

Regards
bosco_yip

Reply | Quote

A very interesting idea

I see a problem with it with it as a general technique since:
Internal spaces may be trimmed
It requires that the length of each segments be “small” compared to the 50 spaces. A couple larger subsets could start throwing them off.

Steve

Reply | Quote

Perfect guys! Thank you so much. Much appreciated.

Reply | Quote

I was trying to modify this formula to split my data as attached, but can not.
Understand I can still use text to column which not my first preference.

Any helps would be appreciated,

kind regards

Reply | Quote

1] A3 =PSP, 062 36900 72 2518 0522:01 PSP – PMI NAD

2] B3, enter the formula and copy across :

=TRIM(MID(SUBSTITUTE(MID($A3,FIND(“,”,$A3)+2,FIND(“:”,$A3)-FIND(“,”,$A3)-2),” “,REPT(” “,50)),COLUMN(A:A)*50-49,50))

Regards
Bosco

Reply | Quote

It’s very great!
Thanks very much Bosco,

I hope sometime I can understand the logic, because with previous one I can’t, eventhough have spent hours

Indra

Reply | Quote

Dear Bosco,

Hope this is not too much, but actually this can’t work for text like this

SEC MCH, 062 36900 71 2695 0522

without ” : ” at the end

regards

Reply | Quote

Try………

B3 :

=TRIM(MID(SUBSTITUTE(MID($A3,FIND(“,”,$A3)+2,IF(ISNUMBER(FIND(“:”,$A3)),FIND(“:”,$A3)-FIND(“,”,$A3)-2,255)),” “,REPT(” “,50)),COLUMN(A:A)*50-49,50))

Regards
Bosco

Reply | Quote

It works perfectly for all cases of mine,

Thanks a bunch Bosco,

Indra

Reply | Quote