Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Separate text and numbers

  1. #1
    New Member
    Join Date
    Jan 2014
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Separate text and numbers

    Hi,

    I want to separate the text and numbers into two different cells. Basically, I have the entire drawing details in one cell and I need to split it up, e.g. I have:

    KEEP PLATE 902 4 0002
    STOOL 525 4 0199
    PACK PLATE 525 4 0200
    PACK PLATE 525 4 0201
    PACK PLATE 525 4 0202
    plz help

  2. #2
    Board Regular Momentman's Avatar
    Join Date
    Jan 2012
    Location
    Nigeria
    Posts
    3,961
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Separate text and numbers

    Are they all structured as TEXT followed by numbers? meaning you need KEEP PLATE, STOOL, PACK PLATE e.t.c right?
    Using Excel 2007 ,2010,2013 Windows 7 - 64bit

    You can be whatever you think you can

  3. #3
    Board Regular DILIPandey's Avatar
    Join Date
    Jul 2013
    Location
    Dubai
    Posts
    1,336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Separate text and numbers

    Hi Ravi,

    Try using below formula, enter using ctrl shift enter key combination:-

    =MID(A1,MIN(IFERROR(ISNUMBER(MID(A1,ROW(1:99),1)*1)*(SEARCH(MID(A1,ROW(1:99),1)*1,A1)),"")),99)

    where a1 is given text.

    Regards,
    DILIPandey

  4. #4
    Board Regular Momentman's Avatar
    Join Date
    Jan 2012
    Location
    Nigeria
    Posts
    3,961
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Separate text and numbers

    Maybe

    Excel 2010
    ABC
    1abc 123abc123
    2defgg jkl 123defgg jkl123
    3PACK PLATE 65445 535PACK PLATE65445 535

    Sheet7



    Array Formulas
    CellFormula
    B1{=LEFT(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1)),0)-2)}
    C1{=RIGHT(A1,LEN(A1)-MATCH(TRUE,ISNUMBER(1*MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1)),0)+1)}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself

    Last edited by Momentman; Jan 11th, 2014 at 02:46 AM.
    Using Excel 2007 ,2010,2013 Windows 7 - 64bit

    You can be whatever you think you can

  5. #5
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,340
    Post Thanks / Like
    Mentioned
    84 Post(s)
    Tagged
    31 Thread(s)

    Default Re: Separate text and numbers

    Give these formulas a try...

    For the Text: =TRIM(LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1))

    For the Numbers: =MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),99)
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  6. #6
    New Member
    Join Date
    Jan 2014
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Separate text and numbers

    I want text and number in two different cell

    For example

    PACK PLATE 525 4 0200

    Result i want:-

    Pack plate in one cell and 525 4 0200 in another cell


  7. #7
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,340
    Post Thanks / Like
    Mentioned
    84 Post(s)
    Tagged
    31 Thread(s)

    Default Re: Separate text and numbers

    Quote Originally Posted by ravikr1980 View Post
    I want text and number in two different cell

    For example

    PACK PLATE 525 4 0200

    Result i want:-

    Pack plate in one cell and 525 4 0200 in another cell

    Our messages may have crossed each other... see Message #5
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  8. #8
    Board Regular Momentman's Avatar
    Join Date
    Jan 2012
    Location
    Nigeria
    Posts
    3,961
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Separate text and numbers

    Quote Originally Posted by Rick Rothstein View Post
    Give these formulas a try...

    For the Text: =TRIM(LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1))

    For the Numbers: =MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),99)
    Nicely done as always Rick. I guess the reason you did A1&"0123456789" was to avoid #VALUE where some numbers were not found in the text being searched. Brilliant.

    I dont know how you do it, but you just always do it..........now am laughing at my huge array formula in message #4
    Using Excel 2007 ,2010,2013 Windows 7 - 64bit

    You can be whatever you think you can

  9. #9
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,340
    Post Thanks / Like
    Mentioned
    84 Post(s)
    Tagged
    31 Thread(s)

    Default Re: Separate text and numbers

    Quote Originally Posted by Momentman View Post
    Nicely done as always Rick. I guess the reason you did A1&"0123456789" was to avoid #VALUE where some numbers were not found in the text being searched.
    Thank you for your nice comment. And yes, that is exactly the reason for concatenating the numbers onto the value in the cell... to give the FIND function something to find. This protects against a cell's text not having a number, but it also protects against a cell having no text meaning you can copy the formula down past your existing data in preparation for future data that may be added below the currently existing data.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

Some videos you may like

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
  •