Tax Number Formats

Sharid · May 6, 2018

In my worksheet I have to collect different tax number,

In column T it states what type of tax number it is E.G.

“This is a Foreign Tax Number”
“I cannot issue a tax number”
“This is a Social Security Number ”
“This is an Individual Tax Identification Number ”
“U.S. Employer Identification Number”

Column U has the tax number in it, the problem is the data comes from a web form, I don’t know PHP to change the formats on the form end. I get the data as an excel sheet.

I need to sort the data in the correct format E.G

“This is a Foreign Tax Number” = No changes need as format will depend on countries
“I cannot issue a tax number” = Highlight whole Row in red text

“This is a Social Security Number ” Need to be in this format XXX-XX-XXXX
“This is an Individual Tax Identification Number ” Also in XXX-XX-XXXX

“U.S. Employer Identification Number” Needs to be in this format XX-XXXXXXX

So if a customer submits 123456789 in Column U and Column T states it is either
“This is a Social Security Number ”
“This is an Individual Tax Identification Number ”

Then the format will change to 123-45-6789

If it states it is a “U.S. Employer Identification Number” then the format is 12-3456789

Foreign numbers formats don’t changes and for numbers not supplied then whole row font turns red.

I’m sure this can be done with a formula, but I will be copying and pasting the date from the downloaded file to the worksheet, so I rather not use formulas but a VBA code run by a command button

Thanks all for having a look

Peter_SSs · May 7, 2018

I might be losing track of this a bit but try this version.

Code:

Sub Format_Data()
  Dim c As Range, cNum As Range
  Dim bIs9Digits As Boolean
  
  Application.ScreenUpdating = False
  For Each c In Range("T2", Range("T" & Rows.Count).End(xlUp))
    Set cNum = c.Offset(, 1)
    If IsEmpty(cNum.Value) Then
      c.EntireRow.Font.Color = vbRed
    Else
      bIs9Digits = cNum.Value Like String(9, "#")
      cNum.Value = UCase(cNum.Value)
      Select Case LCase(c.Value)
        Case "this is a social security number", "this is an individual tax identification number"
          If bIs9Digits Then
            cNum.Value = Format(CLng(cNum.Value), "000-00-0000")
          Else
            c.EntireRow.Interior.Color = vbYellow
            c.EntireRow.Font.Color = vbRed
          End If
        Case "u.s. employer identification number"
          With cNum
            If bIs9Digits Then
              .Value = Format(cNum.Value, "00-0000000")
            Else
              c.EntireRow.Interior.Color = vbYellow
            End If
          End With
        Case "i cannot issue a tax number"
          c.EntireRow.Font.Color = vbRed
        Case Else
          'Do nothing
      End Select
    End If
  Next c
  Application.ScreenUpdating = True
End Sub

Sharid · May 7, 2018

Peter

Your the man, this is super. Thanks for sorting this out for me. I could not have done this without you.

Once again Thank you very much

Peter_SSs · May 7, 2018

Sharid said:
Peter

Your the man, this is super. Thanks for sorting this out for me. I could not have done this without you.

Once again Thank you very much

Cheers. Glad we got there in the end. Thanks for letting us know.

Sharid · May 9, 2018

Peter

Your code above does a super job, however I have noticed a problem and it was my fault for not thinking of it sooner.

As you know the U.S tax numbers have to be in the following format

XXX-XX-XXXX OR XX-XXXXXXX

I asked for the code to reformat the number, so if 123456789 was submitted by a user then it would change either to 123-45-6789 or 12-3456789 depending on what was placed in column T.

The second part changes U.S tax numbers (ONLY) to Yellow Fill and Red Font if the number is too short or long. So the user has submitted less than 9 digits or more than 9 digits.

Example
User submits 12345 or 12345678910 then the row will change to YELLOW FILL and RED FONT, this was done to ID errors.
My problem is this.
If the user submits the correct format, meaning the dashes are included it count these dashes a digits and shows that row as an error and changes it to YELLOW FILL and RED FONT - This should not happen

This is the problem
123-45-6789 = 11 digits, the code is counting the dashes and shows row as error
12-3456789 = 10 digits, the code is counting the dash and shows row as error

Example (THIS BIT IS CORRECT)
123456789 Will change to 123-45-6789 or 12-3456789 depending on column T
12345678 row will change to YELLOW FILL and RED FONT, this was done to ID errors.
1234567891 row will change to YELLOW FILL and RED FONT, this was done to ID errors

If you get the time could you have one last look, if not I will make do.

Thanks

Peter_SSs · May 10, 2018

We must be sneaking up on the solution,

Try

Code:

Sub Format_Data_v2()
  Dim c As Range, cNum As Range, UR As Range
  Dim bIs9Digits As Boolean
  Dim s As String
  
  Application.ScreenUpdating = False
  Set UR = ActiveSheet.UsedRange
  With UR
    .Interior.Color = xlNone
    .Font.Color = 0
  End With
  For Each c In Range("T2", Range("T" & Rows.Count).End(xlUp))
    Set cNum = c.Offset(, 1)
    If IsEmpty(cNum.Value) Then
      c.EntireRow.Font.Color = vbRed
    Else
      s = Replace(cNum.Value, "-", "")
      bIs9Digits = s Like String(9, "#")
      cNum.Value = UCase(cNum.Value)
      Select Case LCase(c.Value)
        Case "this is a social security number", "this is an individual tax identification number"
          If bIs9Digits Then
            cNum.Value = Format(CLng(s), "000-00-0000")
          Else
            With Intersect(UR, c.EntireRow)
              .Interior.Color = vbYellow
              .Font.Color = vbRed
            End With
          End If
        Case "u.s. employer identification number"
          With cNum
            If bIs9Digits Then
              .Value = Format(CLng(s), "00-0000000")
            Else
              Intersect(UR, c.EntireRow).Interior.Color = vbYellow
            End If
          End With
        Case "i cannot issue a tax number"
          Intersect(UR, c.EntireRow).Font.Color = vbRed
        Case Else
          'Do nothing
      End Select
    End If
  Next c
  Application.ScreenUpdating = True
End Sub

Sharid · May 10, 2018

Super, this has done the job. Thank you very much

Peter_SSs · May 10, 2018

Sharid said:
Super, this has done the job.

Let's hope so.

But if there are further issues, post back again.

Tax Number Formats

Sharid

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

Excel Facts

Sharid

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

Sharid

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

Sharid

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Tax Number Formats

Well-known Member

MrExcel MVP, Moderator

Excel Facts

Well-known Member

MrExcel MVP, Moderator

Well-known Member

MrExcel MVP, Moderator

Well-known Member

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock