I have a table which has a range of codes, this varys from 1 through to 16, this is then followed by a letter, for example:
11A - Reconnect
15b telephone
1b reconnect
2c
Some data has two numbers at the beginning and some has a single number. The formula I managed to get working up to 10. after this it doesn’t pull through the full number.
=SUBSTITUTE(J2,RIGHT(J2,LEN(J2)-MAX(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9,10},J2),""))),"")
I’ve tried to add "11, 12, 13, 14, 15, 16" to the formula and that doesn’t work either.
I’m sure this is probably something really simple so apologies if I’m sounding a little dumb but I’ve tried everything I can think of with no luck.
My formula to extract the letter seems to work fine:
=IF(L2="INVALID CODE","",LEFT(TEXTAFTER(J2, L2),1))
>Solution :
How about this:
IF(ISNUMBER(LEFT(A1,2)*1),LEFT(A1,2)*1,LEFT(A1,1)*1)
So the isnumber checks for 2 numbers as the first two characters, if that evaluates to false then the left() takes 1 character.
So, these versions keep the character:
