Variable length numeric extraction from a String

[dfuser]

I have the following data set that I need to be able to extract the numeric values after a hyphen...

nnnn-IF8 (aa); return 8
nnnn-OF8 (aa); return 8
nnnn-IB16 (aa); return 16
nnnn-OB32 (aa); return 32
nnnn-IR6I (aa); return 6
nnnn-IT6I (aa); return 6

I'm looking for a single formula... any help is greatly appreciated! Thank you.

 

[XOR LX]

Hi.

In all of your examples the desired extraction is followed by a space and then the string "(aa)". I take it this is always the case? If not, can you give more examples, or clarify some other way?

Regards

 

[njimack]

I can't take the credit for the below, I copied it from post 37 here:http://www.mrexcel.com/forum/excel-...t-only-numbers-text-string-4.html#post4015942

Excel Workbook
	A	B
1	nnnn-IF8 (aa)	8
2	nnnn-OF8 (aa)	8
3	nnnn-IB16 (aa)	16
4	nnnn-OB32 (aa)	32
5	nnnn-IR6I (aa)	6
6	nnnn-IT6I (aa)	6
Sheet1

 

[Rick Rothstein]

This formula seems to work...

=LOOKUP(9.9E+307,--LEFT(MID(A1,FIND("-",A1)+MIN(FIND({0,1,2,3,4,5,6,7,8,9},MID(A1,FIND("-",A1),99)&3^45))-1,99),ROW($1:$99)))

 

[dfuser]

Hi.

In all of your examples the desired extraction is followed by a space and then the string "(aa)". I take it this is always the case? If not, can you give more examples, or clarify some other way?

Regards

Yes, this is the format of each string... the nnnn and (aa) portion are inconsequential representing numbers or letters (a) in this case. Thank you for your help!

 

[XOR LX]

This formula seems to work...

=LOOKUP(9.9E+307,--LEFT(MID(A1,FIND("-",A1)+MIN(FIND({0,1,2,3,4,5,6,7,8,9},MID(A1,FIND("-",A1),99)&3^45))-1,99),ROW($1:$99)))

 

[dfuser]

I can't take the credit for the below, I copied it from post 37 here:http://www.mrexcel.com/forum/excel-...t-only-numbers-text-string-4.html#post4015942

Excel Workbook
	A	B
1	nnnn-IF8 (aa)	8
2	nnnn-OF8 (aa)	8
3	nnnn-IB16 (aa)	16
4	nnnn-OB32 (aa)	32
5	nnnn-IR6I (aa)	6
6	nnnn-IT6I (aa)	6
Sheet1

Thank you, the only issue with this formula is that the first four (4) characters are numeric as well (I should have been clearer in my original post, sorry about that)... I'd need to find a way to strip those from the result.

 

[Rick Rothstein]

This formula seems to work...

=LOOKUP(9.9E+307,--LEFT(MID(A1,FIND("-",A1)+MIN(FIND({0,1,2,3,4,5,6,7,8,9},MID(A1,FIND("-",A1),99)&3^45))-1,99),ROW($1:$99)))

What is the expression... "Practice what you preach"?

Now, I'm just waiting for the OP to discover the formula I posted.

 

[XOR LX]

Now, I'm just waiting for the OP to discover the formula I posted.

That would be nice!

 

[dfuser]

This formula seems to work...

=LOOKUP(9.9E+307,--LEFT(MID(A1,FIND("-",A1)+MIN(FIND({0,1,2,3,4,5,6,7,8,9},MID(A1,FIND("-",A1),99)&3^45))-1,99),ROW($1:$99)))

Thank you, I'm not having a lot of luck with this... I'm getting a #N/A result.

Let's try my being more specific and see if it's me (which is likely)...

I have 6 cells AL4...AL9 containing the following...

1756-IF8 (AI)
1756-OF4 (AO)
1756-IB16 (DI)
1756-OB32 (DO)
1756-IR6I (RTD)
1756-IT6I (TC)

I'd like to extract for use in calculations the 8, 4 (typo from the first round), 16, 32, 6, and 6 into AM4....AM9

Does that help to clarify my request? Thank you.