Extract a Numeric string from an alphanumeric string

[daniqwerty987]

Hi guys,

I am new in the forum. I am struggling in extracting a 5 digit code from an alphanumeric string. I tried in different ways but it is not working. The format I would like to extract is for example "14.508" and an example of the string could be "aa7fr0V14.508bgt9".

Thanks a lot if you can help me!

 

[Gokhan Aycan]

If it is at the exact place always you can use MID, else a regex would be better. Not very proficient with excel functions, maybe there is a good one for this case.

 

[kweaver]

It would be helpful if you provided several representative examples of your data.

 

[JGordon11]

This will work for a very specific data structure. Must have a six character number (five digits and a decimal) that starts on or before the 50th character in the string. You can see it fails for other data structures. The last example use $500 instead of $50 if the occurrence is late in a long string.

Book1
	A	B
1	aa7fr0V14.508bgt9	14.508
2	aa7fr0V14508bgt9	9
3	aa7fr0V-14.508bgt9	-14.50
4	aa7fr0V+14.508bgt9	+14.50
5	aa7fr0V(1456)bgt9	(1456)
6	aa7fr0V+bgtaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa14.508bbbbbb	#N/A
7	aa7fr0V+bgtaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa14.508bbbbbb	14.508
Sheet1

Cell Formulas
Range	Formula
B1:B5	B1	=MID(A1,MATCH(TRUE,ISNUMBER(--MID(A1,ROW($1:$50),6)),0),6)
B6	B6	=MID(A6,MATCH(TRUE,ISNUMBER(--MID(A6,ROW($1:$50),6)),0),6)
B7	B7	=MID(A7,MATCH(TRUE,ISNUMBER(--MID(A7,ROW($1:$500),6)),0),6)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Rick Rothstein]

I'm with kweaver... post several representative examples. What we cannot tell for sure from your single example... Is there always a decimal point? If so, is the decimal point always between the 2nd and 3rd digits? And, if so, is the number you want always to only number with a decimal point in it? Could there ever be a period (which is basically a decimal point between non-digits) in the text? Also, is a VBA solution acceptable?