Return only numbers directly after specific substring

[RaydenUK]

I could really use some help with a formula... it needs to return the number directly following substring "RX" within a string? For example if i have the string "12354165RX15" then just show "15". Another example is "165163546RX15_G_B" then it would still need to also just show "15". I have tried a few different things with no luck.There shouldn't ever be anymore numbers in the string, other than the one immediately following the "RX". Also note that the number can range from 1-4 digits. Any help would be greatly appreciated.

 

[pgc01]

Hi

Try:

=-LOOKUP(1;-MID(A1,FIND("RX",A1)+2,{1,2,3,4}))

 

[RaydenUK]

Thank you for the quick response but it says that this formula contains an error. I even took out the "-" after the = and it still says there is an error.

 

[RaydenUK]

Great! that works!!!! thank you so much!

 

[Rick Rothstein]

Thank you for the quick response but it says that this formula contains an error. I even took out the "-" after the = and it still says there is an error.

Change the semi-colon to a comma.

 

[pgc01]

I'm glad it helped.

Remark: Sorry, I noticed now that one of the separators in the formula was ";", instead of ",", that your have surely corrected.
I work with 2 versions of excel with both ";" and "," as separators and forgot to change that one.

Should be

=-LOOKUP(1,-MID(A1,FIND("RX",A1)+2,{1,2,3,4}))

 

[Rick Rothstein]

I work with 2 versions of excel with both ";" and "," as separators

Well, that sounds like fun...

 

[pgc01]

Well, that sounds like fun...

Yes

No problem between versions with different separators.
The only problem is when I'm working with the one with the ";" separator and I want to post a formula here.
That's why I usually copy the formulas directly from the immediate window using ActiveCell.Formula.

Sometimes, however, with a small formula I copy directly and replace all (or almost all ) the separators manually, like in this case.

 

[Rick Rothstein]

No problem between versions with different separators.

All I know is that if I had to work with those two versions, I would be continually typing commas when semi-colons were required and vice versa... especially in formulas with lots of whichever, such as a long array constant. Speaking of array constants, commas (for me) are "column" dividers and semi-colons are "row" dividers... for the version using semi-colons where I use commas, what get's used in the array constants for the "row" dividers?

 

[pgc01]

Speaking of array constants, commas (for me) are "column" dividers and semi-colons are "row" dividers... for the version using semi-colons where I use commas, what get's used in the array constants for the "row" dividers?

With the ";" I have the backslash "\" as column divider and the semicolon ";" as the row divider.

For ex., for L1:N2:

{"L1"\"M1"\"N1";"L2"\"M2"\"N2"}

Really funny working with both sets of dividers