+ Reply to Thread
Results 1 to 6 of 6

Splitting Text and Numbers from the one cell

  1. #1
    Forum Contributor
    Join Date
    11-20-2009
    Location
    Melbourne
    MS-Off Ver
    Excel 365
    Posts
    260

    Splitting Text and Numbers from the one cell

    Is there a formula or function that I can use that will allow me to split data like below into two separate cells?

    In one cell, I have - Narre Warren Black 6.8-44
    & in another cell - Pakenham Maroon 5.4-34 just as an example. (There are quite a number of cells like this)
    I would like the cell split to show - Narre Warren Black in one cell and then 6.8-44 in the next cell.
    The same is needed with the following cell - Pakenham Maroon in one cell and 5.4-34 in the next cell.

    Because some have one space and some have 2 or 3 I can't use 'LEFT' and I can't use Text to Columns as far as I can see.

    Could someone advise of a way of doing this please?

    Thank you for any assistance offered.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Splitting Text and Numbers from the one cell

    With your value in A1 try

    =MID(A1,1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&1234567890))-2)
    Does that work for you?

    Oops, second part.
    If that's in B1
    =TRIM(SUBSTITUTE(A1,B1,""))
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Splitting Text and Numbers from the one cell

    With your sample data in A1:A2

    This formula extracts the name:
    Please Login or Register  to view this content.
    and this one extracts the numeric part:
    Please Login or Register  to view this content.
    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Splitting Text and Numbers from the one cell

    Interesting, in this case Excel allows either {} or () for the array.

  5. #5
    Forum Contributor
    Join Date
    11-20-2009
    Location
    Melbourne
    MS-Off Ver
    Excel 365
    Posts
    260

    Re: Splitting Text and Numbers from the one cell

    Thank-you so much. Both work perfectly.

  6. #6
    Registered User
    Join Date
    01-15-2015
    Location
    cincinnati, oh
    MS-Off Ver
    2013
    Posts
    2

    Re: Splitting Text and Numbers from the one cell

    Hello All,

    I have something similar, but I have much more of a complex splitting of text.

    The following needs to be sorted:

    TRANSFAXTOR COMPANY 50389921 30 164 N $1,800.00

    FAKE COMPANY NAME / INVOICE / PAY TERMS / PAST DUE / SHORTPAID (Y/N) / AMOUNT


    The original looks like this:
    TRANSFAXTOR COMPANY 50389921 30 164 N $1,800.00

    Is there any help with this?

    Thanks in advance.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Splitting Inconsistent Text and Numbers With No Delimiters
    By clhockey5779 in forum Excel Formulas & Functions
    Replies: 21
    Last Post: 09-03-2014, 05:52 PM
  2. [SOLVED] Splitting numbers and text to their respective columns
    By JEAN1972 in forum Excel General
    Replies: 7
    Last Post: 07-31-2014, 06:48 AM
  3. Replies: 4
    Last Post: 07-25-2014, 12:59 AM
  4. Replies: 19
    Last Post: 07-20-2014, 04:55 PM
  5. [SOLVED] Splitting a cell between letters and numbers.
    By matty88 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-26-2013, 07:47 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1