Edit Replace the third digit only of a number
 

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.

Edit Replace the third digit only of a number
 

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(TEX T(A1,"General"),LEN(TEXT(A1,"General"))-1)&"9"),A1)

"Hammertime" wrote:

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.

Edit Replace the third digit only of a number
 

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Hammertime" wrote:
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.

Edit Replace the third digit only of a number
 

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.

In article ,
Hammertime wrote:

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.