Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert a number of rows based on a value in a cell on active row | Excel Discussion (Misc queries) | |||
cell color index comparison | New Users to Excel | |||
number formatted cell | Excel Discussion (Misc queries) | |||
Maximum Number of Cell Formats | Excel Discussion (Misc queries) | |||
Defining a number in a cell by text then subtracting it by the tex | Excel Worksheet Functions |