Return only numbers from a cell that contains both "numbers" and "text".

[Eriktoinfinity]

I know you technically can't have numbers AND text in a cell but let's say I have the following cells in the first column:

[TABLE="class: grid, width: 400, align: left"]
<tbody>[TR]
[TD]004 BRD HSE[/TD]
[TD]004[/TD]
[/TR]
[TR]
[TD]PLT BRD SD[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]040 RPL CUS[/TD]
[TD]040[/TD]
[/TR]
</tbody>[/TABLE]






And I want to pull the 3 leftmost characters into the next column only if they are numbers (as shown). Is that possible?

Related to this is I want it to convert them to a number so there are no leading "0"s.

I created a very basic macro that pulled back numbers and then "paste special - values" to get rid of the formulas, but the leading "0"s stayed and I can't get rid of them even when I select them and convert to number through right-clicking and "Format Cells". Any help would be appreciated.

 

[Rick Rothstein]

Give this formula a try...

=IF(AND(ISNUMBER(-MID(A1,{1,2,3},1))),LEFT(A1,3),"")

Edit Note
-----------------
For those who might be considering this simpler formula...

=IF(ISNUMBER(-LEFT(A1,3)),LEFT(A1,3),"")

be advised that it is not foolproof as it will return an incorrect result for this text string (and others like it)...

1E4 BRD HSE

 

[Eriktoinfinity]

Thank you very much, Rick! That did it!

 

[Eriktoinfinity]

Rick, I hate to take up more of your time, but can you explain to me what the "-" in front of the MID indicates as well as the "{}"? I just want to understand so I can utilize those in the future.

 

[Rick Rothstein]

Rick, I hate to take up more of your time, but can you explain to me what the "-" in front of the MID indicates as well as the "{}"? I just want to understand so I can utilize those in the future.

The MID function returns a text value even when what it returns it looks like a number, so the ISNUMBER function see it as text unless something is done to make it a number. Excel will convert text constants that look like a number to a real number if you involve in a mathematical operation... placing the minus sign in front of MID does that (it is the equivalent of multiplying by minus one). So, if MID return text, trying to multiply it by minus one will not result in a real number, so ISNUMBER would return FALSE for it; however, when MID returns text that look like a number, multiply it by minus one will result in a real number (the negative of the number the text looks like)... we do not care about the magnitude of the number that gets returned, on in the fact that it is a number (ISNUMBER only returns TRUE or FALSE, not what the number is).

The {1,2,3} part of my formula is an array constant consisting of three independent values... when the AND function sees an expression with an array constant, it will evaluate the expression substituting each value individually into the overall expression within its parentheses and then evaluate all three of those expressions taken together for each being all TRUE. In effect, this part of my formula...

AND(ISNUMBER(-MID(A1,{1,2,3},1)))

becomes this when IF evaluates it...

AND(ISNUMBER(-MID(A1,1,1)),ISNUMBER(-MID(A1,2,1)),ISNUMBER(-MID(A1,3,1)))

Since each MID function is returning a single character, each ISNUMBER is actually evaluating whether the character returned by its MID function is a digit or not... the AND will return TRUE only if each ISNUMBER returns TRUE (meaning three digits were returned.