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