CODEWITHSUNDEEP
vbaexcel
Talamtran
Talamtran

Reputation: 13

Insert character based on criteria

First time poster here...I need to add hyphens to numbers if they meet one criteria or another. Based on the criteria met, the dashes would be added to different locations.

The numbers are in column A, the values they are checked against are in column B. I need to check the value in B then update A accordingly. End result if B=SOC SEC would be 123-45-6789 or if B=TAXID it would be 12-3456789.

I have the below, but I know I am missing several components. I can't seem to find the correct answer anywhere.

Sub AddHyphens()
    Dim TaxIDType As String
    Dim taxid As Long
    Dim SOCSEC As String
    Dim TAXIDfield As String
        IF TaxIDType="SOC SEC" then Cstr(Left(TaxID,3)&"-"&Mid(TaxID,4,2)&"-"&Right(TaxID,4)
     Else
        IF TaxIDType="TAXID" then Cstr(Left(TaxID,2)&"-"&Right(TaxID,7)

End Sub

Upvotes: 1

Views: 59

Answers (1)

Andres Felipe Martinez
Andres Felipe Martinez

Reputation: 323

Here you go. Just make sure you select the whole range of numbers you need converted in Column A.

Sub AddHyphens()
Dim myRange As Range
Dim myCell As Range

Set myRange = Selection

For Each myCell In myRange

If myCell.Offset(0, 1).Value = "SOC SEC" Then
    myCell.Value = Left(myCell.Value, 3) & "-" & Mid(myCell.Value, 4, 2) & "-" & Right(myCell.Value, 4)
ElseIf myCell.Offset(0, 1).Value = "TAXID" Then
     myCell.Value = Left(myCell.Value, 2) & "-" & Right(myCell.Value, 7)
End If

Next myCell

End Sub

Upvotes: 1

Related Questions