+ Reply to Thread
Results 1 to 4 of 4

Separate numbers from a varying range of text within a cell

  1. #1
    Registered User
    Join Date
    02-18-2014
    Location
    SF, CA
    MS-Off Ver
    Excel 2010
    Posts
    5

    Separate numbers from a varying range of text within a cell

    I have a string of data in a cell that is always three numbers, followed by a space, and then a varying number of words, with spaces between them. I want to separate the numbers from the varying string of words, keeping the word string together. For example:

    I have:
    123 Department Black Cat
    and I want:
    "123" in one cell, and "Department Black Cat" in another

    Another example is, I have:
    456 Charming
    and I want
    "456" in one cell, and "Charming" in another.

    do you have ideas / suggestions for this? I'd usually replace space with a character and then text--> column, but this time I need to keep the second piece together, and this seems like a long work-around.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2405 Win 11 Home 64 Bit
    Posts
    23,873

    Re: Separate numbers from a varying range of text within a cell

    Assume your data is in column A.

    In column B: =LEFT(A1,3)

    In column C: =RIGHT(A1,LEN(A1)-4)

    Then copy down.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,499

    Re: Separate numbers from a varying range of text within a cell

    This will return your number regardless of length...
    =LOOKUP(9E+99+307,--MID(A2,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A2&1234567890)),ROW(INDIRECT("1:"&LEN(A2))
    Not sure yet on the text.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Separate numbers from a varying range of text within a cell

    maybe this

    in B1

    =TRIM(REPLACE(A1,FIND(" ",A1),100,""))

    in C1

    =TRIM(SUBSTITUTE(A1,B1,""))
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

+ 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] AND OR help. Reporting Truth from varying text strings in two separate columns.
    By Zza1pqx in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-03-2012, 11:56 AM
  2. [SOLVED] Separate numbers from text in a cell
    By Karnik in forum Excel General
    Replies: 10
    Last Post: 10-07-2012, 08:26 PM
  3. Separate numbers embed in a text string into a new column or cell
    By DennyT48 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-23-2012, 04:14 PM
  4. separate and identify text from numbers in one cell
    By freud1 in forum Excel General
    Replies: 2
    Last Post: 06-05-2009, 08:17 AM
  5. Select a varying range of text and paste into a cell
    By JesterPrime in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-13-2005, 07:05 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