Extracting a number from a text string and treating it as a number (NOT text)

[doogooroo]

Hi,

I have a column of data consisting of example sentences like the following:

• E.g. "SAD_Sentence_01_My_uncle_plays_golf_on_Fridays"

Each sentence has a specific number that identifies it. The number is always 2 digits and immediately follows the text string "Sentence_".

I want to extract the sentence number as a number not text. I have been experimenting but I can only extract the number as a text string using the following formula. (NB: the formula assumes G3 contains the sentence.)

• =MID(G3,SEARCH("Sentence_",G3)+9,2)

(Disclaimer: I don't really understand the above formula. Found something similar here on Excel Forum and just tinkered with it. That said, it seems to work except the output is in text format.)

Does anyone know a way of extracting the two digit number following the "Sentence_" text string while still treating it as a number? (By the way, it's fine for "01" to become a simple old "1".)

I know I can copy and paste the text output then convert it to a number, but I'd rather avoid the hassle if possible.

I'd appreciate any suggestions.

Cheers,

Dougal

[Rubeus_Hagrid]

Hi,

Please try this :

=VALUE(MID(G3,SEARCH("Sentence_",G3)+9,2))

[martindwilson]

or
=MID(G3,SEARCH("Sentence_",G3)+9,2)+0
or
=--MID(G3,SEARCH("Sentence_",G3)+9,2)

[rasonline]

the value function is the safest. I also usually multiply the answer by 1 and this seems to be the easiest.

[doogooroo]

Rubeus_Hagrid, Martindwilson and Rasonline, thank you!

All the formulas suggested so far work. I've gone with:

• =VALUE(MID(G3,SEARCH("Sentence_",G3)+9,2))

at Rasonline's suggestion it's safest.

Thanks again!

Cheers,

Dougal

[Richard Schollar]

That's an interesting statement that VALUE is the 'safest'. There are some differences between the two, but the most compelling I think is that using VALUE uses up one of your nested function allowance. It doesn't matter so much if you are using xl2007, but in 2003 or lower, you retain more flexibility by using the -- or +0 coercers (or *1).

Richard

[rasonline]

That's an interesting statement that VALUE is the 'safest'. There are some differences between the two, but the most compelling I think is that using VALUE uses up one of your nested function allowance. It doesn't matter so much if you are using xl2007, but in 2003 or lower, you retain more flexibility by using the -- or +0 coercers (or *1).

Richard

Indeed you are correct. i was not considering that it uses up one of the nested functions. thanks for that one.