Results 1 to 3 of 3

Thread: Split text

  1. #1
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    In attached wb, I want to split the text in to 2 columns. There are abt 2000 rows and length of text is variable. What formula should I use?
    Attached Files Attached Files
    Regards
    Prasad

  2. #2
    3 Star Lounger
    Join Date
    Feb 2003
    Location
    Runcorn, Cheshire, United Kingdom
    Posts
    372
    Thanks
    0
    Thanked 2 Times in 2 Posts
    [quote name='prasad' post='794544' date='23-Sep-2009 12:58']In attached wb, I want to split the text in to 2 columns. There are abt 2000 rows and length of text is variable. What formula should I use? [/quote]

    Are you doing this once, or do you want to do it many times?

    If once, I'd probably use the Text to Columns command and specify the open square bracket as the Delimiting character.
    Once it was then split I'd select column C and perform a Find-Replace to change the close square bracket to an empty char.


    If you want to do it by formula
    Cell B2 containing
    =FIND(A2, FIND("[")-1)
    and Cell C2 containing
    =MID(A2,FIND("[",A2)+1,LEN(A2)-FIND("[",A2)-1)
    should do what you want

  3. #3
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='Gfamily' post='794553' date='23-Sep-2009 18:24']Are you doing this once, or do you want to do it many times?

    If once, I'd probably use the Text to Columns command and specify the open square bracket as the Delimiting character.
    Once it was then split I'd select column C and perform a Find-Replace to change the close square bracket to an empty char.


    If you want to do it by formula
    Cell B2 containing
    =FIND(A2, FIND("[")-1)
    and Cell C2 containing
    =MID(A2,FIND("[",A2)+1,LEN(A2)-FIND("[",A2)-1)
    should do what you want[/quote]
    Thanks, it works fine. I was trying to split data using Text to column, but forgot to text delimiter as "[". No need to use formula.

    Thanks a lot.
    Regards
    Prasad

Posting Permissions

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