Last text word to be manipulated

[nutrastat]

Having now worked, unsuccessfully, for the last 4 hours on a formula, I am wondering whether anyone can now help.

In cell A1 we need to check for the word "DECK" as the 1st four characters, if this is TRUE then we MAY need to amend it in cell A4. If it says DECK 1, DECK 2, DECK 3 and so on to DECK 9, then we need to put a 0 (zero) in the front of the number (i.e. DECK 01, DECK 02 and so on). However, if it says DECK 13, then we do not need to zero (this is all about a sorting problem).

Unfortunately, the bit that I am finding difficult, is that some Decks do not have numbers, they may be called DECK A, or DECK B, and, to make it even harder it may be called DECK AZURE, DECK BATA and these will NOT need a zero in front.

Can anyone help?

 

[Aladin Akyurek]

Something like:

B2:

=IF(ISNUMBER(REPLACE(A2,1,4,"")+0),"DECK "&TEXT(REPLACE(A2,1,4,"")+0,"00"),A2)

 

[dave3009]

Beaten to it but what the hey

=IF(AND(ISNUMBER(SUBSTITUTE(A1,"DECK ","")+0),LEN(SUBSTITUTE(A1, "DECK ", ""))=1), "DECK 0" &SUBSTITUTE(A1,"DECK ",""), A1)

Not nearly as elegant

 

[nutrastat]

Many thanks for such a prompt response. I will now try to look at your solution to find out how it works, as I have never used number & replace functions before; which is why I probably had so many difficulties.

Best regards,

Graham

 

[nutrastat]

Hi Dave3009,

Many thanks for your answer as well; it is also appreciated.

Br

Graham