Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find the 1st occurance of a number in a cell
The cell's text is:
Reclassed to 101-001-4455-003. I want to find the position of the first number, which in this case would be the number "1" and it would be in the 14th place (if I counted right!). The text will vary, so the locate of the number will vary, and the number itself will vary. I should be able to figure this out, but it's become like looking at your best friend and not being able to remember their name! Thanks for anyone's assistance! Yours truly.....Cynthia :-) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find the 1st occurance of a number in a cell
Commit both of these functions as array formulas (CTRL+SHIFT+ENTER)
To find the position of the first numerical value: =MATCH(TRUE,ISNUMBER(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),0) And then to extract that number from the string, use the above formula embedded in the MID() function: =MID(A1,MATCH(TRUE,ISNUMBER(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),0),1) -- Regards, Dave "lovemuch" wrote: The cell's text is: Reclassed to 101-001-4455-003. I want to find the position of the first number, which in this case would be the number "1" and it would be in the 14th place (if I counted right!). The text will vary, so the locate of the number will vary, and the number itself will vary. I should be able to figure this out, but it's become like looking at your best friend and not being able to remember their name! Thanks for anyone's assistance! Yours truly.....Cynthia :-) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find the 1st occurance of a number in a cell
I was hoping for a much simpler solution using SEARCH, FIND, MID, by using a
wildcard for any number. I just can't find what such a wildcard would be. Is it "--"? I get the rest of your suggestion, though. Thanks for your help! "David Billigmeier" wrote: Commit both of these functions as array formulas (CTRL+SHIFT+ENTER) To find the position of the first numerical value: =MATCH(TRUE,ISNUMBER(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),0) And then to extract that number from the string, use the above formula embedded in the MID() function: =MID(A1,MATCH(TRUE,ISNUMBER(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),0),1) -- Regards, Dave "lovemuch" wrote: The cell's text is: Reclassed to 101-001-4455-003. I want to find the position of the first number, which in this case would be the number "1" and it would be in the 14th place (if I counted right!). The text will vary, so the locate of the number will vary, and the number itself will vary. I should be able to figure this out, but it's become like looking at your best friend and not being able to remember their name! Thanks for anyone's assistance! Yours truly.....Cynthia :-) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find the 1st occurance of a number in a cell
I forgot to do the formula as an array...now it works for me. Just seems a
bit complex! Oh well, if it works! Thanks, David "David Billigmeier" wrote: Commit both of these functions as array formulas (CTRL+SHIFT+ENTER) To find the position of the first numerical value: =MATCH(TRUE,ISNUMBER(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),0) And then to extract that number from the string, use the above formula embedded in the MID() function: =MID(A1,MATCH(TRUE,ISNUMBER(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),0),1) -- Regards, Dave "lovemuch" wrote: The cell's text is: Reclassed to 101-001-4455-003. I want to find the position of the first number, which in this case would be the number "1" and it would be in the 14th place (if I counted right!). The text will vary, so the locate of the number will vary, and the number itself will vary. I should be able to figure this out, but it's become like looking at your best friend and not being able to remember their name! Thanks for anyone's assistance! Yours truly.....Cynthia :-) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find the 1st occurance of a number in a cell
If there will always be at least one number within the text string,
try... =MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")) Otherwise, try... =IF(OR(ISNUMBER(FIND({0,1,2,3,4,5,6,7,8,9},A2))),M IN(FIND({0,1,2,3,4,5,6, 7,8,9},A2&"0123456789")),"") Hope this helps! In article , lovemuch wrote: The cell's text is: Reclassed to 101-001-4455-003. I want to find the position of the first number, which in this case would be the number "1" and it would be in the 14th place (if I counted right!). The text will vary, so the locate of the number will vary, and the number itself will vary. I should be able to figure this out, but it's become like looking at your best friend and not being able to remember their name! Thanks for anyone's assistance! Yours truly.....Cynthia :-) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
Instead of a negative number, I'd like to show zero... | Excel Worksheet Functions | |||
Find last occurance of text in range | Excel Worksheet Functions | |||
First Number in a Cell | Excel Worksheet Functions | |||
Possible Lookup Table | Excel Worksheet Functions |