MID A STRING - STARTING POINT IS DEFINED BY THE NUM_CHAR IS UNDEFINED.

[vishrajv]

Hi All,
I am new to MRExcel and this is my 1st Post. I am looking for a help regarding a mid function.
For example :- EBA//92X:RATE:0.99999A://92X:RATE:0.667766B://92X:RATE:9898.18544444A://
I want to extract 0.0667766 in this example but the number can be different for other cases.

I open for formula or vba.

Please help me solve this

 

[Rick Rothstein]

Will the value you want ALWAYS be located between the 2nd and 3rd double-slash marks (//) like shown in your single example?

 

[Anthony47]

Using 365 and its fresh TEXTBEFORE and TEXTAFTER, if the original text is in A2:

=TEXTBEFORE(TEXTAFTER(A2,"RATE:",2),"B://")

For the other versions:

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A2,"RATE:",REPT(" ",100)),"B://",REPT(" ",100)),200,100))

If they don't do the job then please answer Rick's questions (see above)

 

[vishrajv]

value will always ending with ://

 

[Rick Rothstein]

value will always ending with ://

That does not help given that all your values end with ://. Your original question says you are trying to find one specific value, but are you actually saying that you want to retrieve all of the values that end that way?

 

[vishrajv]

Will the value you want ALWAYS be located between the 2nd and 3rd double-slash marks (//) like shown in your single example?

That does not help given that all your values end with ://. Your original question says you are trying to find one specific value, but are you actually saying that you want to retrieve all of the values that end that way?

every value start with :// so i can assume that the value

That does not help given that all your values end with ://. Your original question says you are trying to find one specific value, but are you actually saying that you want to retrieve all of the values that end that way?

num_char in simple logic should be the next : after my mid

 

[Rick Rothstein]

What you want is still not clear. Here is your example...

EBA//92X:RATE:0.99999A://92X:RATE:0.667766B://92X:RATE:9898.18544444A://

Originally, you said you wanted the one I highlighted in red. Now you are saying you want the "next : after my mid". What "mid" are you referring to? I am trying to determine if you only want the red highlighted number or if you want that one and the two blue ones also. If you only want the red one, I am trying to understand how you determine that is the one you want. My original question basically asked if you wanted the second number after the colon ). So, please clarify what it is that you want.

 

[Anthony47]

And did you try what in msg #3?