Insert a 0, if not a number.

[Gil149]

Guys,
I need some help on finding and inserting a number in between characters. I need to go through the A column, cell by cell, and if the second to last character from the right is not a number, I need to insert number 0.

Example...
02AA1
02A34
02AA5

Would become...
02AA01
02A34
02AA05

I can find the cells, code wise...but I don't know how to add the 0...while maintaining the exact cell data!

 

[Gerald Higgins]

=IF(ISNUMBER(MID(A1,LEN(A1)-1,1)+0),A1,LEFT(A1,LEN(A1)-1)&"0"&RIGHT(A1,1))

There may be a more elegant way . . .

 

[Jonmo1]

Perhaps

=IF(ISNUMBER(MID(A1,LEN(A1)-1,1)+0),A1,REPLACE(A1,LEN(A1),0,0))

 

[CharlesChuckieCharles]

=if(isnumber(right(A1,2)),A1,left(A1,len(A1)-1) & "0" & right(A1,1))

 

[Gil149]

Thanks guys! I didn't even think about that method! I am to used to the code end...really appreciate it.

 

[Jonmo1]

=if(isnumber(right(A1,2)),A1,left(A1,len(A1)-1) & "0" & right(A1,1))

This assumes that the last character in A1 is also a number, which was not established in the OP..
It's true for the 3 sample strings given, but I don't think that's a large enough sample to establish that assumption.

But if that's a true statement, that the last character will always be a number then I suggest a combination of Charles' and My Formulas

=IF(ISNUMBER(RIGHT(A1,2)+0),A1,REPLACE(A1,LEN(A1),0,0))

 

[Jonmo1]

Thanks guys! I didn't even think about that method! I am to used to the code end...really appreciate it.

Glad to help, thanks for the feedback.