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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Mark Christensen" <(E-Mail Removed)> wrote in message news:uU$(E-Mail Removed)...
> 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
>
>

How about a formula?


"Ron de Bruin" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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
>
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.nl/tips.htm
>
>
> "Mark Christensen" <(E-Mail Removed)> wrote in message
> news:uU$(E-Mail Removed)...
>> 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

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.

"Ron de Bruin" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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
>
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.nl/tips.htm
>
>
> "Mark Christensen" <(E-Mail Removed)> wrote in message
> news:uU$(E-Mail Removed)...
>> 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

Try

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


--
Regards,

Peo Sjoblom



"Mark Christensen" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> How about a formula?
>
>
> "Ron de Bruin" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> 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
>>
>>
>> --
>>
>> Regards Ron de Bruin
>> http://www.rondebruin.nl/tips.htm
>>
>>
>> "Mark Christensen" <(E-Mail Removed)> wrote in message
>> news:uU$(E-Mail Removed)...
>>> 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
>
>

Try this:

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

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Mark Christensen" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> How about a formula?
>
>
> "Ron de Bruin" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> 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
>>
>>
>> --
>>
>> Regards Ron de Bruin
>> http://www.rondebruin.nl/tips.htm
>>
>>
>> "Mark Christensen" <(E-Mail Removed)> wrote in message
>> news:uU$(E-Mail Removed)...
>>> 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
>
>

>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



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Mark Christensen" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
>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.
>
> "Ron de Bruin" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> 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
>>
>>
>> --
>>
>> Regards Ron de Bruin
>> http://www.rondebruin.nl/tips.htm
>>
>>
>> "Mark Christensen" <(E-Mail Removed)> wrote in message
>> news:uU$(E-Mail Removed)...
>>> 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
>
>

Thanks everyone! One of the formulas worked as expected.


"Mark Christensen" <(E-Mail Removed)> wrote in message
news:uU$(E-Mail Removed)...
> 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
>

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" <(E-Mail Removed)> wrote in message
news:%23%(E-Mail Removed)...
> Try this:
>
> =IF(LEFT(RIGHT(A1,2))="3",--REPLACE(A1,8,1,"5"),A1)
>
> --
> HTH,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
> "Mark Christensen" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> How about a formula?
>>
>>
>> "Ron de Bruin" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>>> 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
>>>
>>>
>>> --
>>>
>>> Regards Ron de Bruin
>>> http://www.rondebruin.nl/tips.htm
>>>
>>>
>>> "Mark Christensen" <(E-Mail Removed)> wrote in message
>>> news:uU$(E-Mail Removed)...
>>>> 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
>>
>>
>
>

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

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Peo Sjoblom" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:%23%(E-Mail Removed)...
>> Try this:
>>
>> =IF(LEFT(RIGHT(A1,2))="3",--REPLACE(A1,8,1,"5"),A1)
>>
>> --
>> HTH,
>>
>> RD
>>
>> ---------------------------------------------------------------------------
>> Please keep all correspondence within the NewsGroup, so all may benefit !
>> ---------------------------------------------------------------------------
>> "Mark Christensen" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>>> How about a formula?
>>>
>>>
>>> "Ron de Bruin" <(E-Mail Removed)> wrote in message
>>> news:%(E-Mail Removed)...
>>>> 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
>>>>
>>>>
>>>> --
>>>>
>>>> Regards Ron de Bruin
>>>> http://www.rondebruin.nl/tips.htm
>>>>
>>>>
>>>> "Mark Christensen" <(E-Mail Removed)> wrote in message
>>>> news:uU$(E-Mail Removed)...
>>>>> 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
>>>
>>>
>>
>>
>
>