Edit Replace the third digit only of a number

H

Hammertime

If I have long list of three digit numbers in an Excel column that have the
number 8 in them how can I replace the 8 in the last digit (eg 458) without
changing the second digit when there is an 8 within it (eg 488). I've tried
"text to columns" & then concatenating but this disrupts the formular.
Thanks for any help you can provide.
 
D

Dennis

Create this formula in a new column and then drag down. Copy and paste
special values only over your existing column when you are happy with the
results.
In this example my list of numbers are in column A and I am creating my new
column in B - replacing the rightmost 8 with a 9

=IF(RIGHT(TEXT(A1,"General"),1)="8",VALUE(LEFT(TEXT(A1,"General"),LEN(TEXT(A1,"General"))-1)&"9"),A1)
 
M

Max

Maybe something like this in B1, copied down:
=IF(RIGHT(A1)+0=8,LEFT(A1,2)&SUBSTITUTE(RIGHT(A1)+0,8,"E"),A1)
which replaces the 3rd digit: 8 with the letter E

If you have data in A1 down:
458
488
884

then B1 down returns it as:
45E
48E
884

Adapt to suit.
 
J

JE McGimpsey

One way:

Assume list is in column A. In column B:

B1: =IF(MOD(A1,10)=8,FLOOR(A1,10)+x,A1)

where x is the digit you want instead of 8.

Copy down. Copy column B. Paste Special/Values over column A. Delete
column B.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top