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

Eriktoinfinity

New Member
Joined
Apr 1, 2014
Messages
6
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:

004 BRD HSE004
PLT BRD SD
040 RPL CUS040

<tbody>
</tbody>






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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,504
Office Version
2010
Platform
Windows
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
 
Last edited:

Eriktoinfinity

New Member
Joined
Apr 1, 2014
Messages
6
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,504
Office Version
2010
Platform
Windows
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.
 
Last edited:

Forum statistics

Threads
1,082,040
Messages
5,362,804
Members
400,693
Latest member
jenlj

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top