Need text in column fixed

[solo_clipper]

I am a novice but have been lurking here a while. Can't find help for this one, should be simple for you guys. Have a column of one word text that looks like this, PHIL3C, SCHM2P, KOLL1C and so on. Evey once in a while I have an error such as PHILC3, the last letter and number reversed. I need a function to examine the cell and correct to PHIL3C. Help please.

[Carim]

Hi,

As long as all the valid names are stored somwhere, you can have a Data Validation List which will prevent all mistakes ...

HTH
Carim

[VBA Noob]

Hi,

If your text changes this should work as it

1). Checks first 4 letters are text
2). Checks that the string length is 6 characters
3). Checks that the 5th character is a number
4). Checks that the last character isn't a number

=AND(ISTEXT(LEFT(A1,4)),COUNTIF(A1,"??????")=1,RIGHT(A1,1)<>{"1",",2","3","4","5","6","7","8","9","0"},OR(MID(A1,5,1)={"1",",2","3","4","5","6","7","8","9","0"}))

VBA Noob

[SteveG]

If you are only concerned with the last two characters in the string you could try,

=IF(ISNUMBER(VALUE(RIGHT(A1,1))),LEFT(A1,LEN(A1)-2)&RIGHT(A1,1)&MID(A1,LEN(A1)-1,1),A1)



HTH

Steve

[daddylonglegs]

...or another way....

=IF(ISERR(RIGHT(A1)+0),A1,REPLACE(LEFT(A1,5),5,0,RIGHT(A1)))

[solo_clipper]

Thanks guy's all is well!

[VBA Noob]

Glad you got your answer

VBA Noob