Return the values between text characters

[ermccarthy]

Good Evening!

I am struggling with a formula, hoping someone can point me in the right direction.

In a cell I have a text string that says "#15 of 214 H.." I need to extract the two numbers into their own cells. In this example 15 and 214 respectively. However, the numbers could be as few as 1 digit or as many as 3 digits.

Therefore I am hoping to use the characters around the digits to get the numbers. So in theory, return the value between "#" and " of" and the value between "of " and " H".

Can someone at least point me in the right direction?

I greatly appreciate any help!

 

[Ron Coderre]

As long as the pattern is constant...
With a test string in A1....eg #15 of 214 H

These regular formulas return the first and second numbers:

B1: =MID(A1,2,SEARCH(" of",A1)-1)
C1: =AGGREGATE(14,6,--MID(A1,SEARCH(" of",A1)+4,{1,2,3}),1)

In that example...
B1: 15
C1: 214

Is that something you can work with?

 

[Michael M]

Excel 2007
	A	B	C
1	"#15 of 214 H.".	15	214
Sheet3

Cell Formulas
Range	Formula
B1	=MID(A1, FIND("#",A1)+1, FIND(" ", A1, FIND("#", A1)+1)-FIND("#",A1)-1)
C1	=MID(A1, FIND("of ",A1)+3, FIND(" H", A1, FIND("of ", A1)+1)-FIND("of ",A1)-3)

 

[Ron Coderre]

Hmm...didn't even consider that the text might have characters before the #.
With
A1: abc [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1 5[/URL] of 214 H

These regular formulas return the first and second values

B1: =AGGREGATE(14,6,--MID(A1,SEARCH("#* of",A1)+1,{1,2,3}),1)
C1: =AGGREGATE(14,6,--MID(A1,SEARCH("#* of ",A1)+{5,6,7,8},{1;2;3}),1)

Returned values:
15
214

For A1: abcdef #1 of 2 H
1
2

For A1: abcdef #1 00 of 222 H
100
222

Is that something you can work with?

 

[bosco_yip]

Maybe,

A1 : abcdef # 100 of 222 H

1] B1, formula :

=-LOOKUP(,-MID(A1,FIND("#",A1)+1,ROW($1:$9)))

2] C1, formula :

=-LOOKUP(,-MID(A1,FIND("of",A1)+2,ROW($1:$9)))

Regards
Bosco

 

[ermccarthy]

This worked PERFECTLY!!! Thank you very Much!! Honestly, I am not sure I would have gotten without your help. Thank you!