=LEFT(A1,LEN(A1)-1)&"B"

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Lost in Microbiology" wrote in message
...
|I am trying to create a list that auto numbers each row. I have to duplicate
| one column into another, but change the last letter in the string from an "A"
| to a "B". Where I get tripped up, is when the column changes from 1A, 2A,
| etc. to a double digit 10A, 11A, etc. I tried using =left(text, 1)&"B", but
| when it goes to double digit numbers my solution fails.
|
| Basically I have data in column A and the same thing with the last character
| change needs to go into column E. So it should look like this:
| A B C D E
| Tube MRN ACC Tube
| 1A 0000 00-000 1B
| 2A 0000 00-000 2B
| 3A 0000 00-000 3B
|
| Thanks for any suggestions.