#1




Trying to change the last digit in a column of 10 digit entries
I have a column of 10 digits and some of the end with 00 (double zero). I need to find each entry and change the last digit to a 5 (five) leaving the other 9 digits the same. I'm not sure how, in Find & Replace to just change the last digit. I used question marks for the first 8 characters and then the double zeros to find the entries but can't figure out what to enter for the Replace field?
any ideas Thanks 
#2




Try:
=Right(A1,9)&"5" 
#3




I appreciate your answer. It took out the first digit and added the 5. I tried doing a =LEFT(A1,9)&"5" and this did what I was looking to do.
Thanks for your help. 
#4




Maybe the others will give a better formula that will search values in a range that has "00" at the end of the string then replace the last 0 to 5. The simplistic formula above will apply to all strings without regard to the value of the last 2 digits.

#5




Code:
=substitute(right(a1,2),"00","05") 
#6




That looked right to me at first, but on second thought doesn't it just provide the last two digits? That is, if A1 has "6677889900", your formula will return not "6677889905" (which is what I suppose the OP wants) but just "05".
Pretty close, though; easy enough to fix it: Code:
=LEFT(A1,8)&SUBSTITUTE(RIGHT(A1,2),"00","05") 
#7




=a1+(mod(a1,100)=0)*5
__________________
Cheers, Paul Edstein [Fmr MS MVP  Word] 
#8




MOD works great if the column is numeric. But everyone else assumed it was string, and the OP seemed to accept that in his one reply.

#9




I don't believe whether the string is formatted as numbers or text makes any difference for the MOD formula. I see no basis for an assumption they're not formatted as numbers though.
__________________
Cheers, Paul Edstein [Fmr MS MVP  Word] 
#10




Originally I was going to ask whether the OP had numbers or text. But Marcia offered a solution using the RIGHT function, and the OP replied "No, but LEFT works; thanks". So I decided if LEFT worked, it must be a string value, not text.
Macropod, are you saying MOD will work with string values, too (as long as the string represents a number)? I didn't realize that. 
#11




Precisely.
__________________
Cheers, Paul Edstein [Fmr MS MVP  Word] 
Thread Tools  
Display Modes  

Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
long digit error  Midasbelize  Excel  1  05162017 10:14 AM 
Changing Distance Between a Digit and it's Subscript  mohsen.amiri  Word  2  01222017 12:59 AM 
Selecting a digit from a code  Warren99  Excel  2  05292016 04:08 AM 
Regex/wildcard search for dates with 2digit and 4digit years  Marrick13  Word VBA  2  01292016 07:04 AM 
A challenging digit by digit manipulation, rotate the digit in range of 0 to 9  laucn  Excel Programming  14  05172015 12:12 PM 