+ Reply to Thread
Results 1 to 5 of 5

Separating text from numbers

  1. #1
    Registered User
    Join Date
    03-17-2010
    Location
    Sebastopol, MS
    MS-Off Ver
    Excel 2007
    Posts
    14

    Separating text from numbers

    I have a problem: Say I have letters and numbers mixed together in a column, and I just want the letters before the numbers...
    alp144 alp
    city123 city
    mom344 mom
    tha233a tha
    tlc234 tlc
    vacpm234a vacpm

    Is this even possible? Thanks in advance for any help.

    Matt

  2. #2
    Valued Forum Contributor mdbct's Avatar
    Join Date
    11-11-2005
    Location
    CT
    MS-Off Ver
    2003 & 2007
    Posts
    848

    Re: Separating text from numbers

    Please see this post, from this thread

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

    Re: Separating text from numbers

    Try this
    =LEFT(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890))-1)
    where your value is in A1. Does that work for you?
    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

  4. #4
    Registered User
    Join Date
    03-17-2010
    Location
    Sebastopol, MS
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Separating text from numbers

    Okay, thanks. That works. Can you explain this formula? I would be interested to know how it works...

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

    Re: Separating text from numbers

    If you're speaking of the one I presented
    =LEFT(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890))-1)

    Okay, breaking it apart somewhat, The FIND Function will find the position of a given value and return that position. (We don't know yet exactly what value is in the first numerical position yet).

    The LEFT function takes everything to the left of a named position (in this case the value found by FIND -1.

    The combination of MIN and FIND will return the lowest value found in that FIND Array [1,2,3,....}. However, if one of those digits that the FIND function is looking for is not there (for example alp144alp does not contain 0,2,3,5,6,7,8 or 9), the equation will return an error. To avoid that, we concatinate 1234567890 to the end of A1 in the equation so that we know that all digits will be represented (A1&1234567890).

    So for alp144alp, the formula would simplify to
    LEFT("alp144alp",MIN(FIND({1,2,3,4,5,6,7,8,9,0},"alp144alp123456789"0-1)

    LEFT("alp144alp",MIN(4, 11,12,5,14,15,16,17,18,19)-1

    LEFT("alp144alp",3)

    Clear as mud now?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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