reference designators

[22strider]

Hello Friends

Could anyone please help me with the following?
I have list of electronic components with their reference designators against them (the list is actually a bill of materials of a circuit board). All the reference designators for a particular component are in one cell only.
For example if a resistor has 5 reference designators (R1, R2, R32, R54, R15), they are given in one cell only.
Now the problem is that some of the designators are missing the letter “R” in them. So in our example the designators may be given as (R1, 2, 32, R54, 15) rather than (R1, R2, R32, R54, R15). Now, not all the components will have letter R in them, the capacitors will have C in them.
The good thing is that whichever letter is required to be put in front of the designators is given at least once in one or more designators.
Following table is to show sample of how it should be:
Part Number Type Qty Current - Reference Designator Correct - Reference Designator
12345 resistor 5 R1, 2, R32, R56, 45 R1, R2, R32, R56, R45
56879 Capacitor 4 C1, 3, 4, C6 C1, C3, C4, C6


Thanks for your time
Rajesh

 

[Gary McMaster]

Hi Rajesh,

I believe the following code sample does what you're asking. You may have to make some changes in the code depending on which columns contain your reference designations and part type descriptions. I have zillions of part lists hanging around that identify components the same way you do and they all have the same inconsistencies. The code could be improved but it gets the job done for me as is and I have no reason to spend time improving it.

I've seen some amazing formulas in this forum. It wouldn't surprise me at all to see someone come along with a code free solution.

Hope this helps.

Gary

Public Sub Test()

Dim oRefDes As Range
Dim oCell As Range
Dim vSplit As Variant
Dim lCount As Long
Dim sPrefix As String

'Assumes each term in string is seperated by a comma or a comma and a space

'Change all instances of "D" in next statement to letter of column containing ref designations
Set oRefDes = ActiveSheet.Range("D2:D" & ActiveSheet.Range("D" & Rows.Count).End(xlUp).Row)

For Each oCell In oRefDes

    'Change "-2" next line to indicate offset from "ref des" column to description column
    Select Case UCase(oCell.Offset(0, -2).Value) ' Assumes part desription is 2 cols left of ref des
    
        Case "RESISTOR" 'All case statements upper case
            sPrefix = "R"
        Case "CAPACITOR"
            sPrefix = "C"
        Case "TRANSISTOR"
            sPrefix = "Q"
        Case "DIODE"
            sPrefix = "D"
        Case "INTEGRATED CIRCUIT"
            sPrefix = "U"
        Case "CONNECTOR"
            sPrefix = "J"
        'Case <Case statements for whatever else>
            'sPrefix = "Whatever"
        Case Else
            sPrefix = "?" 'Unknown part description
    End Select
    
    'Adjust cell text to insure each ref des is seperated by ", " (comma/space)
    'Replace comma with comma/space then trim out any multiple spaces
    oCell.Value = Replace(oCell.Value, ",", ", ")
    oCell.Value = Application.WorksheetFunction.Trim(oCell.Value)
    
    vSplit = Split(oCell.Value, " ") ' Split the string at the spaces
    
    'If the 1st character in each ref des is a number then add the alpha prefix
    For lCount = LBound(vSplit) To UBound(vSplit)
        If IsNumeric(Left(vSplit(lCount), 1)) Then
            vSplit(lCount) = sPrefix & vSplit(lCount)
        End If
    Next lCount
    
    oCell.Value = ""
    
    'Reassemble array in target cell
    For lCount = LBound(vSplit) To UBound(vSplit)
        oCell.Value = oCell.Value & " " & vSplit(lCount)
    Next lCount

    'Remove leading space added in above loop
    oCell.Value = Mid(oCell.Value, 2)
    
Next oCell

End Sub

 

[22strider]

Hello Gary

The code worked very nicely.

Thanks for your help. It will be very useful.
Rajesh

 

[Gary McMaster]

Rajesh,

Thanks for the feedback. I'm glad it worked for you.

We sometimes have reference designators that look like R1, R3, R10-16, R19. The code does not consider this "hyphenated range". You could modify this code to split the designator string at the hyphen to achieve the same results (R1, R3, R10-R16, R19).

Searching for the hyphen could be included in the original procedure but I would be inclined to make it a separate procedure that goes through the parts list a second time in order to keep the code as simple as possible. I've never seen a parts list so large that the user would even notice a delay in traversing the list twice.

Gary

 

[22strider]

We have the hyphenated ranges as well. I'll try modifying the code.

Thanks again
Rajesh

 

[Gary McMaster]

We have the hyphenated ranges as well. I'll try modifying the code.

Please don't hesitate to post back or send me a private message if you have any problems with the modification.

Gary