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 6, 2018

Give this a try in a copy of your workbook.

Code:

Sub Format_Data()
  Dim c As Range
  
  Application.ScreenUpdating = False
  For Each c In Range("T2", Range("T" & Rows.Count).End(xlUp))
    Select Case LCase(c.Value)
      Case "this is a social security number", "This is an Individual Tax Identification Number"
        c.Offset(, 1).Value = Format(CLng(c.Offset(, 1).Value), "000-00-0000")
      Case "u.s. employer identification number"
        c.Offset(, 1).Value = Format(CLng(c.Offset(, 1).Value), "00-0000000")
      Case "i cannot issue a tax number"
        c.EntireRow.Font.Color = vbRed
      Case Else
        'Do nothing
    End Select
  Next c
  Application.ScreenUpdating = True
End Sub

Sharid · May 6, 2018

sorry posted before I saw your code, have removed my post. I will test your code and let you know.

Thanks for having a look

Sharid · May 6, 2018

Peter this is what I am looking for, just a few issues

1) It does not format the [FONT=&quot]"This is an Individual Tax Identification Number"[/FONT] TO [FONT=&quot]XXX-XX-XXXX[/FONT]

2) If NO TAX number is placed in for [FONT=&quot]"this is a social security number"[/FONT] or [FONT=&quot]"u.s. employer identification number"[/FONT] then it places [FONT=&quot]000-00-0000[/FONT] or [FONT=&quot]00-0000000[/FONT] into column G when it should also highlight these rows in RED as the[FONT=&quot] TAX Number is missing[/FONT].

I assume this would also happen if the code ran for [FONT=&quot]"This is an Individual Tax Identification Number"[/FONT]3) If for U.S numbers the user has MISSED some number 123456 so there are not 9, it will add zeros 123-45-6[FONT=&quot]000. [/FONT]I should have said that it need to ID less that or more than nine numbers for all U.S numbers.

4) I get I type mismatch error if I run the code twice, so if code has already formatted the Numbers and then I add more below an run the code again I get a code mismatch error message on this line.

Code:

c.Offset(, 1).Value = Format(CLng(c.Offset(, 1).Value), "000-00-0000")

I know it a bit much to ask but could you still help, as you are on the right track

Peter_SSs · May 6, 2018

Referring to your numbered points

1. My mistake. I change my code to work with lower case text and forgot to change that one in the code. Fixed below.

2. I missed the part about these rows being red. Fixed that and removed the addition of 000-00-0000 etc

3. I didn't understand this point. Please give more explanation and examples with expected results regarding this point.

4. Yes, that was annoying me too while testing.

Fixed I think.

See if this is closer to the mark.

Code:

Sub Format_Data()
  Dim c As Range
  
  Application.ScreenUpdating = False
  For Each c In Range("T2", Range("T" & Rows.Count).End(xlUp))
    If IsEmpty(c.Offset(, 1).Value) Then
      c.EntireRow.Font.Color = vbRed
    ElseIf IsNumeric(c.Offset(, 1).Value) Then
      Select Case LCase(c.Value)
        Case "this is a social security number", "this is an individual tax identification number"
          c.Offset(, 1).Value = Format(CLng(c.Offset(, 1).Value), "000-00-0000")
        Case "u.s. employer identification number"
          c.Offset(, 1).Value = Format(CLng(c.Offset(, 1).Value), "00-0000000")
        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 6, 2018

Peter

Your code is super, has got rid of all the issues apart from point 3, which you asked for more info.

The U.S tax numbers are in 9 digits so the customer submits 123456789 and it changes to either 123-45-6789 or 12-3456789. This bit is fine and not an issue.

However if the user submits more or less than 9 digits for U.S TAX NUMBER ONLY then before it would crash if more than 9 or add zeros if less than 9 so

This example has 10 numbers U.S TAX NUMBER ONLY
1234567891 = VBA code crashes, this is still an issue it now give an OVERFLOW error message as there are more numbers to format than 9

This example from FIRST CODE has LESS than 9 numbers SO Zeros are added to compensate
If numbers are less than 9 so 1234567, then your FIRST code would add extra zeros to compensate E.G. 1234567 would be reformatted to either 123-45-6700 or

12-3456700 it would add zeros to compensate to less than 9 figures.

Now with your NEW code it does not add the zeros, HOWEVER it still formats the number E.G 1234567 will become 123-45-67 or 12-34567
Since the TAX Numbers are incorrect I would like the row highlighted in Yellow. As the tax numbers are incomplete, either too long or too short. U.S TAX NUMBER ONLY

Apart from that it is bang on.

Peter_SSs · May 6, 2018

OK, so yellow if more or less than 9 digits. What if there is nothing for a US Tax number?
- Yellow text because less than 10, or
- Red text because no number, or
- Yellow fill and red text?

Sharid · May 6, 2018

If there is NO Tax number row has RED TEXT

If Number is more or less than 9 USA tax numbers ONLY then row is Yellow FILL and Red Text.

By highlighting rows I can know which records are incorrect.

Also could you Please fix it so it does not crash if number is larger than 9 U.S Only as foreign Tax Numbers will also be collected, which may be larger than 9 or have letters in them e.g UK has AB123456C,

Can I ask for 1 more request and all Letters are upper case. ab123456c to AB123456C

Sorry for the slow replys, my internet keeps crashing

Peter_SSs · May 6, 2018

Forgot to ask last time.
In post 6 and 8 you have used the terms U.S tax numbers and USA tax number. I guess these are the same as each other but neither was mentioned in the earlier posts. Which of the original terms does this correspond to (guessing either This is an Individual Tax Identification Number or U.S. Employer Identification Number) or is it a new category?

Sharid · May 7, 2018

Peter,

Sorry for the confusion when I mention “Tax Numbers – Tax Number” I am referring to all the different U.S formats, they has 3 types. Two have the same format.

Social Security Number = XXX-XX-XXXX
Individual Tax Identification Number = XXX-XX-XXXX
And
U.S. Employer Identification Number = XX-XXXXXXX

All are 9 digits but the “U.S Employer Identification Number“ has to be in a different formats. Below is the text that will go into the column T and the 9 digits tax number will go into column U

“This is a Social Security Number ”
“This is an Individual Tax Identification Number ”

“U.S. Employer Identification Number”

THIS IS FOR ALL U.S TAX NUMBER/S

Anything ABOVE 9 digits is crashing the code. RATHER THAN crashing can it show in Red Text Yellow Fill for the whole row.

Anything LESS than 9 digits needs to be shows as an error, Red Text Yellow Fill for the whole row.

Also when I mention “Tax Numbers – Tax Number” I am referring ONLY to all the different U.S formats. I will also be collecting foreign TAX NUMBERS so their length and formats will vary depending on their country, so there is no need to do anything to them apart from if Letters have been used then convert them to uppercase E.G.

UK Tax Number
ab123456c Will become AB123456C

Hope this helps

Tax Number Formats

Sharid

Well-known Member

Excel Facts

Peter_SSs

MrExcel MVP, Moderator

Sharid

Well-known Member

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

Sharid

Well-known Member

Similar threads

Forum statistics

Share this page

Tax Number Formats

Well-known Member

Excel Facts

MrExcel MVP, Moderator

Well-known Member

Well-known Member

MrExcel MVP, Moderator

Well-known Member

MrExcel MVP, Moderator

Well-known Member

MrExcel MVP, Moderator

Well-known Member

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