finding position of number in text

**momo123** · 08-07-2009, 03:14 AM

hi..izzit possible to find the position of the 1st number value inbetween text??

example : GBB_5600_OPP_WNN

**DonkeyOte** · 08-07-2009, 03:15 AM

Yes

=MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890))

where A1 holds string

**momo123** · 08-07-2009, 03:18 AM

thank you for the fast reponse! its working fine =)

**momo123** · 08-07-2009, 03:24 AM

hi..sorry..what if the number i want to find is from 5600 and find the position of 5 instead of position of 1 in CA1..how do i avoid that? if methods?

GBB_CA1_5600_OPP_WNN

**DonkeyOte** · 08-07-2009, 03:33 AM

If we assume the position of interest is where you have _ followed by digit then perhaps something like:

Please Login or Register  to view this content.

Not very elegant though... if by any chance you have the morefunc.xll add-in installed you could use a REGEX function to look for the _digit pattern, eg:

Please Login or Register  to view this content.

a tad more succinct

**momo123** · 08-07-2009, 03:43 AM

oh ok can! thanks =)

**romperstomper** · 08-07-2009, 04:10 AM

You could also use the same principle as the original formula I suppose:
=MIN(FIND({"_1","_2","_3","_4","_5","_6","_7","_8","_9","_0"},A1&"_1_2_3_4_5_6_7_8_9_0"))+1

**DonkeyOte** · 08-07-2009, 04:11 AM

you could do but that would be both logical & efficient... so where's the fun in that ?

**romperstomper** · 08-07-2009, 04:20 AM

You're right - don't know what came over me!

**Cheeky Charlie** · 08-07-2009, 07:35 AM

Less is more:

=MIN(FIND("_"&{1,2,3,4,5,6,7,8,9,0},A1&"_1_2_3_4_5_6_7_8_9_0"))+1

Even less:

{=MIN(FIND("_"&ROW(1:9),A1&"_1_2_3_4_5_6_7_8_9_0"))+1}

Least:

{=MIN(FIND("_"&ROW(1:9),A1&"_"&ROW(1:9)))+1}
(though this would be particularly slow in big numbers)