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

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

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.

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.

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:

=a1+(mod(a1,100)=0)*5

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

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.

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]

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.

Precisely.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]