Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-01-2020, 01:10 PM
arkansawyer16 arkansawyer16 is offline Trying to change the last digit in a column of 10 digit entries Windows 10 Trying to change the last digit in a column of 10 digit entries Office 2019
Novice
Trying to change the last digit in a column of 10 digit entries
 
Join Date: Apr 2020
Posts: 2
arkansawyer16 is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 04-01-2020, 03:10 PM
Marcia's Avatar
Marcia Marcia is offline Trying to change the last digit in a column of 10 digit entries Windows 7 32bit Trying to change the last digit in a column of 10 digit entries Office 2013
Expert
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Try:
=Right(A1,9)&"5"
Reply With Quote
  #3  
Old 04-01-2020, 03:26 PM
arkansawyer16 arkansawyer16 is offline Trying to change the last digit in a column of 10 digit entries Windows 10 Trying to change the last digit in a column of 10 digit entries Office 2019
Novice
Trying to change the last digit in a column of 10 digit entries
 
Join Date: Apr 2020
Posts: 2
arkansawyer16 is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 04-01-2020, 03:35 PM
Marcia's Avatar
Marcia Marcia is offline Trying to change the last digit in a column of 10 digit entries Windows 7 32bit Trying to change the last digit in a column of 10 digit entries Office 2013
Expert
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

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.
Reply With Quote
  #5  
Old 04-01-2020, 10:30 PM
ArviLaanemets ArviLaanemets is offline Trying to change the last digit in a column of 10 digit entries Windows 8 Trying to change the last digit in a column of 10 digit entries Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Code:
=substitute(right(a1,2),"00","05")
Reply With Quote
  #6  
Old 04-03-2020, 08:49 AM
BobBridges's Avatar
BobBridges BobBridges is offline Trying to change the last digit in a column of 10 digit entries Windows 7 64bit Trying to change the last digit in a column of 10 digit entries Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

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")
Reply With Quote
  #7  
Old 04-03-2020, 02:16 PM
macropod's Avatar
macropod macropod is offline Trying to change the last digit in a column of 10 digit entries Windows 7 64bit Trying to change the last digit in a column of 10 digit entries Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

=a1+(mod(a1,100)=0)*5
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #8  
Old 04-04-2020, 09:34 AM
BobBridges's Avatar
BobBridges BobBridges is offline Trying to change the last digit in a column of 10 digit entries Windows 7 64bit Trying to change the last digit in a column of 10 digit entries Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

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.
Reply With Quote
  #9  
Old 04-04-2020, 03:20 PM
macropod's Avatar
macropod macropod is offline Trying to change the last digit in a column of 10 digit entries Windows 7 64bit Trying to change the last digit in a column of 10 digit entries Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #10  
Old 04-06-2020, 10:39 AM
BobBridges's Avatar
BobBridges BobBridges is offline Trying to change the last digit in a column of 10 digit entries Windows 7 64bit Trying to change the last digit in a column of 10 digit entries Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

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.
Reply With Quote
  #11  
Old 04-06-2020, 02:03 PM
macropod's Avatar
macropod macropod is offline Trying to change the last digit in a column of 10 digit entries Windows 7 64bit Trying to change the last digit in a column of 10 digit entries Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by BobBridges View Post
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]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
long digit error Midasbelize Excel 1 05-16-2017 10:14 AM
Changing Distance Between a Digit and it's Subscript mohsen.amiri Word 2 01-22-2017 12:59 AM
Selecting a digit from a code Warren99 Excel 2 05-29-2016 04:08 AM
Regex/wildcard search for dates with 2-digit and 4-digit years Marrick13 Word VBA 2 01-29-2016 07:04 AM
Trying to change the last digit in a column of 10 digit entries A challenging digit by digit manipulation, rotate the digit in range of 0 to 9 laucn Excel Programming 14 05-17-2015 12:12 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 09:30 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft