How to Identify based on - on the nth character

[DavidDiazPagan]

Hello, 

This is my first post so apologies if I'm not presenting enough information. I am attempting to improve a workflow that can create a new column to identify whether a specific cell is either a Social Secuirty Number (SSN), an Employer Identification Number (EIN), or neither. SSN's usually have two hyphens in between the first three and second two numbers (XXX-XX-XXXX) and the EIN have only one dash after the first two numbers (XX-XXXXXXX). Is there a tool in Alteryx that can help identify which column is SSN vs. EIN vs. neither? For example:

	New Column
SSN/EIN/Other	Tax ID
111-22-3333	SSN
11-2222222	EIN
22-3333333	EIN
25E7514U	Other

The only way I've figured out to do this is to use text to column in order to get the numbers before the first hyphen and categorize them based on the number of characters but I feel like it's a bit of an extra step and not as accurate. Please let me know if there is an easier way to work on this. 

Thank you so much in advanced!

David

[DavidP · Accepted solution]

This can quite easily be done with a regex function as shown below. Sample workflow attached.

[DavidDiazPagan]

Didn't know about this formula. Thank you so much! Way better than multiple tools to identify these items. Much appreciated!

[cmcclellan]

You would probably want to add some additional criteria in there as well, it depends if the location of the dashes are important.  If they are, you should check the position of the dashes (for SSN) or the single dash (for EIN) as well.

[DNS]

I am using formula "RIGHT(B2,LEN(B2)-FIND(CHAR(1),SUBSTITUTE(B2,"-",CHAR(1),4)))" in excel.. How do I replicate the same in Alteryx?

[cmcclellan]

I think you should create a new topic ;)