Results 1 to 12 of 12
  1. #1
    Star Lounger
    Join Date
    Jan 2007
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Splitting data into columns (2003)

    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.
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Splitting data into columns (2003)

    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.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Splitting data into columns (2003)

    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),<font color=red>1</font color=red>)),FIND(CHAR(1),SUBSTITUTE(A2,"/",CHAR(1),<font color=red>2</font color=red>))-FIND(CHAR(1),SUBSTITUTE(A2,"/",CHAR(1),<font color=red>1</font color=red>))-1)

    The 3rd group (between the 2nd and 3rd "/"):
    =MID(A2,1+FIND(CHAR(1),SUBSTITUTE(A2,"/",CHAR(1),<font color=red>2</font color=red>)),FIND(CHAR(1),SUBSTITUTE(A2,"/",CHAR(1),<font color=red>3</font color=red>))-FIND(CHAR(1),SUBSTITUTE(A2,"/",CHAR(1),<font color=red>2</font color=red>))-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

  4. #4
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Splitting data into columns (2003)

    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

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Splitting data into columns (2003)

    A very interesting idea <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

    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

  6. #6
    Star Lounger
    Join Date
    Jan 2007
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Splitting data into columns (2003)

    Perfect guys! Thank you so much. Much appreciated.

  7. #7
    Star Lounger
    Join Date
    Jul 2005
    Location
    Jakarta, Indonesia
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Splitting data into columns (2003)

    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
    Attached Files Attached Files

  8. #8
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Splitting data into columns (2003)

    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

  9. #9
    Star Lounger
    Join Date
    Jul 2005
    Location
    Jakarta, Indonesia
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Splitting data into columns (2003)

    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

  10. #10
    Star Lounger
    Join Date
    Jul 2005
    Location
    Jakarta, Indonesia
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Splitting data into columns (2003)

    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

  11. #11
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Splitting data into columns (2003)

    Try………

    B3 :

    =TRIM(MID(SUBSTITUTE(MID($A3,FIND(",",$A3)+2,IF(IS NUMBER(FIND(":",$A3)),FIND(":",$A3)-FIND(",",$A3)-2,255))," ",REPT(" ",50)),COLUMN(A:A)*50-49,50))

    Regards
    Bosco
    Attached Files Attached Files

  12. #12
    Star Lounger
    Join Date
    Jul 2005
    Location
    Jakarta, Indonesia
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Splitting data into columns (2003)

    It works perfectly for all cases of mine,

    Thanks a bunch Bosco,

    Indra

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •