Excel Formula to Extract a Number from a Cell

**housinganalyst** · 07-29-2011, 10:49 AM

Hi,

A couple of days ago I posted a request for help with an if statement based on text search (see link at bottom of this post). Some kind posters gave me the solution I needed, which was the following formula:

=IF(ISERROR(IF(ISNUMBER(SEARCH("*",H10490)),MID(H10490,1,FIND("*",H10490)-1),S10490))=TRUE,H10490,IF(ISNUMBER(SEARCH("*",H10490)),MID(H10490,1,FIND("*",H10490)-1),H10490))

However, the problem with this is that the resulting number I get has weird formatting which means I am then unable to carry out a lookup on the number against a list of numbers I have. Does anyone have any thoughts on why this is the case. Even better does anyone have a recommended solution?

Kind regards

HA

P.s. see below for a link to the original request for help:

http://www.excelforum.com/excel-gene...xt-search.html

**Colin Legg** · 07-29-2011, 10:59 AM

Say your sample data is in A1:A4.

To extract the left hand number, in B1 put this formula:

Please Login or Register  to view this content.

Not quite sure if you want the right hand number stored as text or as a true number; in C1 this will make it a true number:

Please Login or Register  to view this content.

Note that, because it is a true number, it would be displayed as 5 rather than 05.

**tigertiger** · 07-29-2011, 11:06 AM

Or Try the formula:

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

**martindwilson** · 07-29-2011, 11:07 AM

i cant see why you are using that long formula !did you not go back and look at the other suggestions!
in b1
=IF(ISNUMBER(FIND("*",A1)),LEFT(A1,FIND("*",A1)-1),A1)
in c1
=IF(ISNUMBER(FIND("*",A1)),MID(A1,FIND("*",A1)+1,255),"") just add 0 to get real numbers
so
in b1
=IF(ISNUMBER(FIND("*",A1)),LEFT(A1,FIND("*",A1)-1),A1)+0
in c1
=IF(ISNUMBER(FIND("*",A1)),MID(A1,FIND("*",A1)+1,255),"")+0