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.

If? consecutive numbers then just use =row()&"B"
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"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.

Try something like this:

=SUBSTITUTE(A1,"A","B")

or this:
=SUBSTITUTE(UPPER(A1),"A","B")

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
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.

=LEFT(A1,LEN(A1)-1) & "B"
--
Gary''s Student - gsnu200750


"Lost in Microbiology" wrote:

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.

=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.