+ Reply to Thread
Results 1 to 7 of 7

Need to find a formula to extract a number out of a cell that also has letters

  1. #1
    Registered User
    Join Date
    01-20-2010
    Location
    Raleigh, NC
    MS-Off Ver
    Excel 2010
    Posts
    21

    Need to find a formula to extract a number out of a cell that also has letters

    Is there a formula that will extract the number and not the letters in a cell?

    Example

    a1 a2
    123 xyz 123

    I need a formula in cell a2 that will pull the 123 only from cell a1.

    Thanks

  2. #2
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,881

    Re: Need to find a formula to extract a number out of a cell that also has letters

    Hi Cassi,

    If the format is always some amount of numeric digits, followed by a space, and you only want to return the numeric digits, you could use:

    =LEFT(A1,FIND(" ",A1)-1)

  3. #3
    Registered User
    Join Date
    01-20-2010
    Location
    Raleigh, NC
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Need to find a formula to extract a number out of a cell that also has letters

    That works great except there are some instances were cell a1 is only numeric so this formula makes it return a #VALUE!

    Is there a way to solve for this problem as well at the same time?


    Thanks

  4. #4
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,881

    Re: Need to find a formula to extract a number out of a cell that also has letters

    Sure, try:

    =IF(ISNUMBER(A1),A1,LEFT(A1,FIND(" ",A1)-1)+0)

  5. #5
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

    Re: Need to find a formula to extract a number out of a cell that also has letters

    =left(a1,find(" ",a1&" ")-1)+0

  6. #6
    Registered User
    Join Date
    01-20-2010
    Location
    Raleigh, NC
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Need to find a formula to extract a number out of a cell that also has letters

    Worked great! Thanks

  7. #7
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

    Re: Need to find a formula to extract a number out of a cell that also has letters

    You're Welcome!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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