Microsoft Office Forums Trying to change the last digit in a column of 10 digit entries
 Register FAQ Search Today's Posts Mark Forums Read

#1
04-01-2020, 01:10 PM
 arkansawyer16 Windows 10 Office 2019 Novice Join Date: Apr 2020 Posts: 2
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
04-01-2020, 03:10 PM
 Marcia Windows 7 32bit Office 2013 Expert Join Date: May 2018 Location: Philippines Posts: 513

Try:
=Right(A1,9)&"5"
#3
04-01-2020, 03:26 PM
 arkansawyer16 Windows 10 Office 2019 Novice Join Date: Apr 2020 Posts: 2

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.
#4
04-01-2020, 03:35 PM
 Marcia Windows 7 32bit Office 2013 Expert Join Date: May 2018 Location: Philippines Posts: 513

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
04-01-2020, 10:30 PM
 ArviLaanemets Windows 8 Office 2016 Expert Join Date: May 2017 Posts: 790

Code:
`=substitute(right(a1,2),"00","05")`
#6
04-03-2020, 08:49 AM
 BobBridges Windows 7 64bit Office 2010 32bit Expert Join Date: May 2013 Location: USA Posts: 695

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
04-03-2020, 02:16 PM
 macropod Windows 7 64bit Office 2010 32bit Administrator Join Date: Dec 2010 Location: Canberra, Australia Posts: 21,931

=a1+(mod(a1,100)=0)*5
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
#8
04-04-2020, 09:34 AM
 BobBridges Windows 7 64bit Office 2010 32bit Expert Join Date: May 2013 Location: USA Posts: 695

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
04-04-2020, 03:20 PM
 macropod Windows 7 64bit Office 2010 32bit Administrator Join Date: Dec 2010 Location: Canberra, Australia Posts: 21,931

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
04-06-2020, 10:39 AM
 BobBridges Windows 7 64bit Office 2010 32bit Expert Join Date: May 2013 Location: USA Posts: 695

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
04-06-2020, 02:03 PM
 macropod Windows 7 64bit Office 2010 32bit Administrator Join Date: Dec 2010 Location: Canberra, Australia Posts: 21,931

Quote:
 Originally Posted by BobBridges Macropod, are you saying MOD will work with string values, too (as long as the string represents a number)?
Precisely.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post Midasbelize Excel 1 05-16-2017 10:14 AM mohsen.amiri Word 2 01-22-2017 12:59 AM Warren99 Excel 2 05-29-2016 04:08 AM Marrick13 Word VBA 2 01-29-2016 07:04 AM laucn Excel Programming 14 05-17-2015 12:12 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:27 PM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top