Extracting specific bits from a cell in Excel

[beesonthewhatnow]

Fairly sure this can be done, but all the formulas I’m finding in online guides either don’t work or I’m doing it wrong. The latter being most likely…

I have a load of cells containing various bits of text. In each there is a reference number starting with the letters “KX”. I need to extract the number following those letters. It’s not always in the same place though - usually the last thing in the cell but not always.

Help!

 

[Ted Striker]

=MID(A1,SEARCH("KX",A1)+2,1)

(Where A1 is the cell with the reference number)

 

[beesonthewhatnow]

I’ve typed that in, but it’s just appearing as exactly that, it’s not performing the formula bit, any ideas why?

 

[beesonthewhatnow]

Worked that out, I was being an idiot.

Is the final 1 in the formula the number of characters after the letters it will return?

 

[Ted Striker]

I’ve typed that in, but it’s just appearing as exactly that, it’s not performing the formula bit, any ideas why?

You may be in non-formula mode...Press ctrl+[the single apostrophe next to the 1 key]

Or the cell could be formatted as text, make sure to give it a number format (highlight the cell and click on the oversized comma symbol in your toolbar, for example)

Make sure the formula starts with = too!

(I think the resolution is much more likely to be the first one tho)

 

[Ted Striker]

Worked that out, I was being an idiot.

Is the final 1 in the formula the number of characters after the letters it will return?

Yes. The +2 represents the length of the KX string btw, so if the length of the string changes, you need to change the +2

 

[beesonthewhatnow]

Think I’ve got it working, thanks