How do I split a cell into text and number

Discussion in 'Excel' started by Hwalker1, Mar 20, 2014.

  1. Hwalker1

    Hwalker1

    Joined:
    Mar 20, 2014
    Messages:
    2
    Likes Received:
    0
    Location:
    England
    I have a table with thousands of lines like this:
    a few words followed by a number
    example: interest repayment 123456
    The text can have many variants in content and length and the number is always a whole number - an integer from 5 to 10 digits long.
    I need to select the number from Col A and reproduce it in an adjacent cell.

    Like:
    Col A ::------------------------- :: Col B
    interest repayment 123456 123456
    All help welcomed
     
    Hwalker1, Mar 20, 2014
    #1
    1. Advertisements

  2. Hwalker1

    XLPadawan

    Joined:
    Jun 9, 2016
    Messages:
    32
    Likes Received:
    5
    Location:
    Allen, TX
    in A1: interest repayment 123456
    Select column A
    Copy
    Paste values into column D​
    Select column D, then Data Ribbon -> Data Tools section -> Text to Columns
    Text to Columns settings:
    Select Delimited
    Set the delimiter to Space and check the box to treat consecutive delimiters as one
    Finish​
    Select a cell SOMEWHERE out to the right of all your text (I chose J1), type =IF(ISNUMBER(D1),D1,0)
    Extend this formula to the right as far as you need it. I extended mine from J1 to M1.​
    in B1: =MAX(J1:M1)
     
    XLPadawan, Jun 13, 2016
    #2
    1. Advertisements

  3. Hwalker1

    XLPadawan

    Joined:
    Jun 9, 2016
    Messages:
    32
    Likes Received:
    5
    Location:
    Allen, TX
    I just had a cool epiphany and found a simpler solution:
    A1 interest repayment 123456
    B1 =VALUE(H1)
    D1 =IFERROR(MID(A1,FIND(" ",A1)+1,99),"")
    Extend the formula in D1 across to H1.
    After extending the formula to the right, H1 should contain:
    =IFERROR(MID(G1,FIND(" ",G1)+1,99),G1)​
     
    XLPadawan, Jun 13, 2016
    #3
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.