 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / May 2008Edit 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. 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) > 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. 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.  SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > 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. 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. > 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 to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.