seperating text
Find bottlenecks in your Excel workbooks
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: seperating text

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Illinois
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I have to seperate some of the items in each cell.......for instance, now it is in as abc123456 I need to get the "abc" into a seperate cell and leave the 123456. I have 30,000 rows to do so I dont really want to do it one at a time.

    Thanks
    Ed.


    [ This Message was edited by: edsjr on 2002-03-28 18:28 ]

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Ed


    Have you tried the Text to Columns.. Wizard found under Data?

    If this won't work for you then just tell us what the criteria is that should be met, eg first 3 characters, only letters etc.

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,823
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

      
    I have to seperate some of the items in each cell.......for instance, now it is in as abc123456 I need to get the "abc" into a seperate cell and leave the 123456. I have 30,000 rows to do so I dont really want to do it one at a time.

    Ed,

    If the alpha part of the entries is of fixed length (it's 3 for "abc123456", the option Data|Text to Columns > Fixed Width will do what you want.

    If the alpha part is of variable length and assuming that the first target entry is in A1,

    In B1 enter:

    =SUBSTITUTE(A1,RIGHT(A1,SUMPRODUCT((LEN(A1)-LEN(SUBSTITUTE(A1,{".",0,1,2,3,4,5,6,7,8,9},""))))),"")

    This will give you the alpha part.

    In C1 enter:

    =SUBSTITUTE(A1,B1,"")+0

    This will give you the numeric part.

    Activate B1 or C1 or both, give a double click on the little black square in the lower right corner of the cell in order to copy down the formula.

    Suppose that you wanted to have the results in B, select all of the cells in B, copy it, and do a Edit|Paste Special >Values to get rid of the formulas.

    Aladin


User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com