Formula/Function to add a character in data column

[IGinc]

I have a DB Column G has Part #'s. When the DB was done (the J was not added, can someone help me with an automated solution formula or function, vba script, to insert J as the 5th character from cell G2-G1270. Thanks in advance.

Examples:

<table width="212" border="0" cellpadding="0" cellspacing="0"><col style="mso-width-source:userset;mso-width-alt:7753;width:159pt" width="212"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:159pt" width="212" height="20">MO83JERHU-Acer-8-1</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">MO83ERHU-Acer-8-2</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">MO83ERHU-Acer-8-3</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">MO83ERHU-Acer-8-4</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">MO83ERHU-Acer-8-5</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">MO83ERHU-Acer-8-6</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">MO83ERHU-Acer-8-7</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">MO83ERHU-Acer-8-8</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">MO83ERHU-Acer-8-9</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">MO83ERHU-Acer-8-10</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">MO83ERHU-Acer-8-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">MO83ERHU-Acer-8-12</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">MO83ERHU-Asus-8-13</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">MO83ERHU-Asus-8-14</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">MO83ERHU-Asus-8-15</td> </tr> </tbody></table>

 

[Rick Rothstein]

You could use this formula (copied down as needed) to create the corrected text, then Copy/Paste Special/Values over top of the existing data.

=LEFT(A1,4)&"J"&MID(A1,5,LEN(A1))

Alternately, if you were looking for a macro solution, select your cells (it is okay if you include blank cells in the selection) and run this...

Sub InsertJ()
  Dim Cell As Range
  For Each Cell In Selection
    If Len(Cell.Value) Then Cell.Value = Left(Cell, 4) & "J" & Mid(Cell.Value, 5, Len(Cell.Value))
  Next
End Sub

 

[VoG]

Try

Sub AddJ()
Dim LR As Long, i As Long
LR = Range("G" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    With Range("G" & i)
        If Mid(.Value, 5, 1) <> "J" Then
            .Value = Left(.Value, 4) & "J" & Right(.Value, Len(.Value) - 4)
        End If
    End With
Next i
End Sub

 

[Scott Huish]

Formula wise:

=REPLACE(G1,5,0,"J")

You could then copy and Paste Special Values over your original data.

 

[IGinc]

Thanks HotPepper - used your solution.