Changing one specific character in cell

[Mark Christensen]

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

 

[Ron de Bruin]

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

 

[Mark Christensen]

How about a formula?

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

 

[Mark Christensen]

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.

 

[Peo Sjoblom]

Try

=--IF(MID(A1,8,1)="3",SUBSTITUTE(A1,3,5,(LEN(A1)-LEN(SUBSTITUTE(A1,3,"")))-(RIGHT(A1,1)="3")),A1)

 

[RagDyer]

Try this:

=IF(LEFT(RIGHT(A1,2))="3",--REPLACE(A1,8,1,"5"),A1)

 

[Ron de Bruin]

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

 

[Mark Christensen]

Thanks everyone! One of the formulas worked as expected.

 

[Peo Sjoblom]

Smart RD

I forgot that REPLACE has the same setup as MID although there is no need
for the quotes in the REPLACE part


--
Regards,

Peo Sjoblom

 

[RagDyer]

I was really waiting for Harlan to chime in, harping on the wasted function
call by using Left(Right(.....) instead of just Mid().<bg>