Need Formula to extract number from text string

Hi there. Your examples may be random. I hope they are... In your real strings - do you have things like 23GHY - with no space between the number and the letter?

OK. Try this. It's a bit of a monster 'cos it's a combination of a couple of things caused by your 23GHY problem and the problem caused by the hyphens. This is is an array formula and must be ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Do uyou need to use the numbers as numbers elsewhere? This formula returns them as text (which you can't add/subtract, etc). The problem is the 12-18 seqence. I'd need to make changes if you want them as real numbers. Anyway take a look and let me know. Solution in Col K. The other cols can be deleted. They're just intermediate steps, retained in case I didn't get it right first time.

Nice approach. If you remove the helper columns - it's still a bit of a monster... albeit about 10 characters shorter than mine...

That certainly makes life a lot simpler...

=MID(LEFT(A2,MAX(IF(ISNUMBER(MID(A2,ROW(1:256),1)+0),ROW(1:256)))),MIN(IF(ISNUMBER(MID(A2,ROW(1:256),1)+0),ROW(1:256))),256)
try this array formula, confirm with shift+Ctrl+Enter

Please Login or Register  to view this content.

Inspired by Zbor

Array formula in B2 and copy down

=MID(LEFT(A2,MATCH(1,-MID(A2,ROW(A$1:A$99),1),1)),MIN(SEARCH(ROW(A$1:A$10)-1,A2&1/17)),99)

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Row\Col	A	B
1	TEXT	RESULT
2	HMP CCD ABC 12-18DWA AAA	12-18
3	HMP CCD 22-18D AAA	22-18
4	ABC CCD BOB 24D NNC	24
5	ABC CCD 28 D NNC	28
6	ALA DND 16G EWSFFSD	16
7	ALA DND 16DWA TRWW	16
8	BBC DND 28 DWA GFHGFHGF	28
9	ALA DND 14 G GFHFHGF	14
10	ALA DND 22G DSA	22

this one is for both tables:

***Array formula

=LOOKUP("zzzzz",MID(LEFT(A2,MATCH(1,-MID(LEFT(A2,LEN(A2)-5),ROW(A$1:A$99),1))),MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&1/17)),ROW($1:$1000)))

I am a little confused. Have you changed your requirements again? Can you repost a FINAL list of Before/After strings so that we can bring this to a close?

Thanks!!