| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to validate a cell to insert from 9 digit number to 13 digit number. | Excel Worksheet Functions | |||
| How to validate a cell to insert from 9 digit number to 13 digit number. | Excel Worksheet Functions | |||
| How to replace 12 digits only of 16 digit number. | Excel Discussion (Misc queries) | |||
| Color a single digit in a mult-digit number cell | Excel Discussion (Misc queries) | |||
| When we enter a 16 digit number (credit card) the last digit chan. | Excel Discussion (Misc queries) | |||
Linear Mode
