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

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.
Similar Threads
  1. Stormy4757
    Replies:
    1
    Views:
    735
    XLPadawan
    Jun 9, 2016
  2. Jorge
    Replies:
    0
    Views:
    564
    Jorge
    Aug 27, 2013
  3. asyluma
    Replies:
    0
    Views:
    472
    asyluma
    Oct 9, 2013
  4. SargeUSMC

    renaming cell ranges

    SargeUSMC, Jul 1, 2014, in forum: Excel
    Replies:
    1
    Views:
    305
    MyHelpster
    Jul 3, 2014
  5. DanM
    Replies:
    1
    Views:
    386
    XLPadawan
    Jun 13, 2016
  6. Tina Yu

    color just "*" in a number cell

    Tina Yu, Aug 15, 2014, in forum: Excel
    Replies:
    0
    Views:
    285
    Tina Yu
    Aug 15, 2014
  7. ALMC612
    Replies:
    0
    Views:
    456
    ALMC612
    Dec 6, 2014
  8. iggydarsa
    Replies:
    0
    Views:
    276
    iggydarsa
    Apr 27, 2015
Loading...