First Number in a Cell
 

I am trying to find a formula that will allow me to extract only the very
first number in a cell. For example, if I have the number 123456 in cell A2,
I would like a formula in cell A1 that will give me only the value 1; if I
have the number 234567 in cell B2, I only want the value 2 in cell B2, and so
on. Further, is it possible to capture more than the first number if I need
more than that?

Thanks, Kelly

If a number is in cell A1 then =LEFT(A1,1) will return its most significant
digit.
--
Gary's Student


"Kelly O." wrote:

I am trying to find a formula that will allow me to extract only the very
first number in a cell. For example, if I have the number 123456 in cell A2,
I would like a formula in cell A1 that will give me only the value 1; if I
have the number 234567 in cell B2, I only want the value 2 in cell B2, and so
on. Further, is it possible to capture more than the first number if I need
more than that?

Thanks, Kelly

Hi

To capture leftmost numeric character as number:
=IF(LEN(A1)=0,"",IF(ISNUMBER(--LEFT(A1,1)),--LEFT(A1,1),""))

To capture 2 leftmost numeric characters as number:
=IF(LEN(A1)=0,"",IF(ISNUMBER(--LEFT(A1,2)),--LEFT(A1,2),""))

To capture 2nd leftmost numeric character as number:
=IF(LEN(A1)=0,"",IF(ISNUMBER(--MID(A1,2,1)),--LEFT(A1,2,1),""))

(Formulas work in cases there exist non-numeric characters in A1 too)


Arvi Laanemets



"Kelly O." wrote in message
...
I am trying to find a formula that will allow me to extract only the very
first number in a cell. For example, if I have the number 123456 in cell
A2,
I would like a formula in cell A1 that will give me only the value 1; if I
have the number 234567 in cell B2, I only want the value 2 in cell B2, and
so
on. Further, is it possible to capture more than the first number if I
need
more than that?

Thanks, Kelly

Though that will return it as text. If you need it to be numeric then use

=--LEFT(A1)

For 2,3,4 numbers etc

=--LEFT(A1,2) subsutituting 2 for 3,4,5,6 etc as required

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------

"Gary's Student" wrote in message
...
If a number is in cell A1 then =LEFT(A1,1) will return its most
significant
digit.
--
Gary's Student


"Kelly O." wrote:

I am trying to find a formula that will allow me to extract only the very
first number in a cell. For example, if I have the number 123456 in cell
A2,
I would like a formula in cell A1 that will give me only the value 1; if
I
have the number 234567 in cell B2, I only want the value 2 in cell B2,
and so
on. Further, is it possible to capture more than the first number if I
need
more than that?

Thanks, Kelly

Hi Kelly,

If your values contain only numbers then use the functions LEFT, RIGHT, MID,
e.g.

=LEFT(A1) if you want the first number as text
or
=--LEFT(A1) if you want the first number as number

=MID(A1,2,1) if you want the second number as text
or
=--MID(A1,2,1) if you want the second number as number

If your values contain both numbers and letters then try this ARRAY formula
(Ctrl+Shift+Enter):

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

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


Regards,
KL



"Kelly O." wrote in message
...
I am trying to find a formula that will allow me to extract only the very
first number in a cell. For example, if I have the number 123456 in cell
A2,
I would like a formula in cell A1 that will give me only the value 1; if I
have the number 234567 in cell B2, I only want the value 2 in cell B2, and
so
on. Further, is it possible to capture more than the first number if I
need
more than that?

Thanks, Kelly