Assuming the part number is in column A,

Private Sub PartNumber_Replace()

Dim lRow As Long

lRow = 1
With ActiveSheet
While .Range("A" & lRow) < ""
If Len(.Range("A" & lRow)) < 16 Then
.Range("B" & lRow) = "C-" & .Range("A" & lRow)
Else
.Range("B" & lRow) = .Range("A" & lRow) & "(OVER)"
End If
lRow = lRow + 1
Wend

End With

End Sub

You can get the same results by using a formula in column B:

=IF(LEN($A1)<16, "C-"&$A1, $A1 & "(OVER)")


"turtle" wrote:

Is it possible to convert this MS Access routine into a Excel macro?
What I would like to do is copy the part number column of an Excel
spreadsheet into a column next to it then add a "C-" as a prefix to
the part number and if the part number is over 16 characters to add
"(OVER)" to the end of it (we will use this to manually convert
these). The Access routine below looks at the length of the part
number then if it is under 16 characters it slaps a "C-" in front of
it and if it is 17 or more characters it pops-up with a warning
message. I don't know how much of this is applicable for a macro. I
haven't tried something like this in Excel and could use some help.
Thanks

Private Sub Part_Number_BeforeUpdate(Cancel As Integer)
Dim strPIM As String
Dim strMsg As String

If IsNull([Part Item Master]) Then
strPIM = [Part Number]
[Value] = strPIM
[Generic] = strPIM
If Len([Part Number]) < 17 Then
[Part Item Master] = "C-" & strPIM
Else
strPIM = Left([Part Number], 16)
strMsg = "The part number is over 16 characters therefore the
GP PIM will" _
& " have to be entered manually"

Response = MsgBox(strMsg, vbOKOnly, "Add GrowthPower Item
Master")

[Part Item Master] = "C-" & strPIM

' [Part Item Master].SetFocus

End If

End If



End Sub