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

1. ## 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  Register To Reply

2. ## 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)  Register To Reply

3. ## 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  Register To Reply

4. ## 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)  Register To Reply

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

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

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

Worked great! Thanks  Register To Reply

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

You're Welcome!  Register To Reply

##### Users Browsing this Thread

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

#### 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