Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 176
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 256
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Urgent date/scheduling calc needed jct Excel Worksheet Functions 3 February 24th 06 02:36 AM
Instead of a negative number, I'd like to show zero... Dr. Darrell Excel Worksheet Functions 6 December 7th 05 09:21 PM
Find last occurance of text in range farutherford Excel Worksheet Functions 5 August 30th 05 02:00 AM
First Number in a Cell Kelly O. Excel Worksheet Functions 4 August 14th 05 03:31 PM
Possible Lookup Table Karen Excel Worksheet Functions 5 June 8th 05 09:43 PM


All times are GMT +1. The time now is 11:07 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"