Test for Number

  • Thread starter Thread starter Ken
  • Start date Start date
K

Ken

Given the following cell data:

A1 100ABC
A2 10BCD
A3 233XVF
A4 6YU
..


Each cell entry begins with one, two or three numbers.

I want to extract the numeric parts of A1, A2, etc. and
place them in B1, B2, etc.

I assume that I would enter into B1 a formula like:
=IF(LEFT(A1,3)="number",LEFT(A1,3),IF(LEFT(A1,2)
="number",LEFT(A1,2),LEFT(A1,1)))

How do I test for "number"?

TIA.
 
If the numbers always are to the left you can use this

=LEFT(A2,MATCH(FALSE,ISNUMBER(--MID(A2,ROW(INDIRECT("1:"&LEN(A2)))+1,1)),0))

entered with ctrl + shift & enter

it will find the first letter and return what's left of it
If you want the position of the first number use

=MATCH(TRUE,ISNUMBER(--MID(A2,ROW(INDIRECT("1:"&LEN(A2)))+1,1)),0)

array entered as well
 
Peo, thanks for the code. I tried it several times and
keep getting the first three characters, including
letters. Is there something in the string that you may
have missed?
 
Back
Top