Looking for a formula to extract a tax id from a string

[lychee]

Hello, I am looking for a formula to extract a numeric tax id (format 11-11111111) from a text string. The strings are very inconsistent, sometimes holding words and dash(es) before or after the tax id.

Example data:
11-11111111
11-11111111
CORP-11-11111111
CORP--11-11111111
CORP 11-11111111
11-11111111 corp
11-11111111-Corp
-corp. 11-11111111
no 1099 under $600 11-11111111
11-11111111 no 1099 under $600
no 1099 11-11111111 under $600

Requested output from each line:
11-11111111

Thanks in advance!

Also thanks for this forum, the information here has been very useful so far!

[martindwilson]

try this udf use as =extractstring(a1)
it should pull any 2digit hypen 8digit string in this format xx-xxxxxxxx

Please Login or Register  to view this content.

[lychee]

After inputting =extractstring(a1) I get #NAME?.

I am not sure if I input the function properly into visual basic. In the editor I pasted the function inside "ThisWorkbook" within VBA Project(excel file name)->Microsoft Excel Objects->ThisWorkbook. Is this correct?

[martindwilson]

paste it in a new module instead
insert/module that should create and open module 1
paste it in there
this workbook has it in

[lychee]

OK I see that it works in the attachment, so now the problem is how to integrate it without having to rebuild the rest of the spreadsheet around the new file. I have saved it in a new module 1 and then saved the entire file as a .xlsm file. On reopening I entered the function (it showed up on the auto fill suggestion drop down), but the result is a blank cell.

[Ron Coderre]

This User Defined Function (UDF) pulls only valid tax ID numbers (##-########)...ignoring patterns like 123-123456789 or 12-123456789...but allowing for values like: A12-12345678G

To use it:
• ALT+F11...to open the VBA Editor
• Select your workbook from the VBAProject list
• Insert.Module
• Copy the below code and paste it into that module

Please Login or Register  to view this content.

To use that UDF to pull the tax ID from cell A1
B1: =PullTaxID(A1)

Is that something you can work with?

[lychee]

Ron,
Unfortunately when using the pulltaxid formula with my data it results with "no match" quite often.

[Ron Coderre]

Can you post some samples that should match...but don't?

[lychee]

Whoops it seems that my example data has 1 too many 1's, there are 2 digits before the - and then 7 digits following.

Martin, I tried changing the line:
.Pattern = "\d{2}\-\d{8}"
to
.Pattern = "\d{2}\-\d{7}"

The results are still blank cells. Where else do I adjust? Thanks!

[Ron Coderre]

This code matches 2 numbers (not preceded by a number) followed by a dash followed by 7 numbers(not followed by a number):

Please Login or Register  to view this content.

[martindwilson]

nothing that reg ex matches the pattern xx-xxxxxxx 2-7 so it should work post a sample like ron said
and i have no idea what US tax numbers look like

[lychee]

Ron-
so close!!!! if the data says

109912-3456789
it says no match
ditto 12-34567891099

The data is so inconsistent

[Ron Coderre]

Ok...maybe this?

Please Login or Register  to view this content.

[lychee]

Spectacular. I am in awe of you both. Cheers!