Only extract numbers from a string of text

[Lost in Microbiology]

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.

 

[Don Guillett]

If? consecutive numbers then just use =row()&"B"

 

[Ron Coderre]

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)

 

[Gary''s Student]

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

 

[Niek Otten]

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

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