 |
 | 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 / New Users / September 2007Changing one specific character in cell
Hello, I've got a spreadsheet of 100's of part numbers and each is 9 characters long. The 8th character in most of them is a 3 but I want to change them all to a 5, is there an easy way to do that? I can't do a simple Replace as 3 may be used more than once in the part number - I just want to change the 3 if it's in the 8th position. Thanks. Mark Hi Mark One way with a macro for A1:A100 Or do you want a formula ? Sub test() Dim cell As Range For Each cell In Range("A1:A100") cell.Value = Mid(cell.Value, 1, 7) & "5" & Mid(cell.Value, 9, 9) Next cell End Sub  SignatureRegards Ron de Bruin http://www.rondebruin.nl/tips.htm > Hello, > [quoted text clipped - 5 lines] > > Mark How about a formula? > Hi Mark > [quoted text clipped - 18 lines] >> >> Mark Try =--IF(MID(A1,8,1)="3",SUBSTITUTE(A1,3,5,(LEN(A1)-LEN(SUBSTITUTE(A1,3,"")))-(RIGHT(A1,1)="3")),A1) SignatureRegards, Peo Sjoblom > How about a formula? > [quoted text clipped - 20 lines] >>> >>> Mark Try this: =IF(LEFT(RIGHT(A1,2))="3",--REPLACE(A1,8,1,"5"),A1) SignatureHTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > How about a formula? > [quoted text clipped - 20 lines] >>> >>> Mark Smart RD I forgot that REPLACE has the same setup as MID although there is no need for the quotes in the REPLACE part SignatureRegards, Peo Sjoblom > Try this: > [quoted text clipped - 24 lines] >>>> >>>> Mark I was really waiting for Harlan to chime in, harping on the wasted function call by using Left(Right(.....) instead of just Mid().<bg> SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > Smart RD > [quoted text clipped - 30 lines] >>>>> >>>>> Mark I tried using that Macro but it's changing all 8th position characters instead of just the cells where 3 is the 8th character. In some cells the 8th character is not a 3 and should not be changed. > Hi Mark > [quoted text clipped - 18 lines] >> >> Mark >The 8th character in most of them is a 3 but I want to change them all to a 5 Then I not read it correct Try this one Sub test2() Dim cell As Range For Each cell In Range("A1:A100") If Mid(cell.Value, 8, 1) = "3" Then cell.Value = Mid(cell.Value, 1, 7) & "5" & Mid(cell.Value, 9, 1) End If Next cell End Sub SignatureRegards Ron de Bruin http://www.rondebruin.nl/tips.htm >I tried using that Macro but it's changing all 8th position characters > instead of just the cells where 3 is the 8th character. In some cells the [quoted text clipped - 22 lines] >>> >>> Mark Thanks everyone! One of the formulas worked as expected. > Hello, > [quoted text clipped - 5 lines] > > Mark |
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.