Splitting SSN without dashes

[Lizbhernan]

I am working on a workflow in which I have to split the SSN (looks like this example 111223333). I am currently using the formula below 

left([Social Security Number],3)+ "-" + Substring([Social Security Number],3,2) + "-" + Right([Social Security Number],4)

Based on the data preview the SSN should come across as 111-22-3333 and then I am using a text to columns tool to split it but I am getting an error stating: SSN: 111-22-3333 lost information in translation.

Can someone help me with what could potentially be wrong with what I am doing?

[Luke_C]

Hi @Lizbhernan 

Can you share some more information? What are the datatypes of all of the columns? Which tool is throwing the warning message?

Based on what you described I can't reproduce the issue, but it's likely related to the datatype or length. 

[Lizbhernan]

The data type on the formula is V-String. I tried using ToNumber() right after to turn it into a number but only the first three numbers show up on the data preview. Once I refresh, I get the error Formula (41) TONUMBER:111-22-3333 lost information in conversion and also Formula (41) TONUMBER:Conversion error limit reached

If the info above doesn't help let me just clear the SSN  and I will upload the workflow I have so you can take a look at it.

[Luke_C · Accepted solution]

Hi @Lizbhernan 

Got it, so you are doing the tonumber() before the text to columns? That may be the reason why, the tonumber function wouldn't know how to interpret the hyphens.  I've attached a few ways to split this that hopefully help

[Lizbhernan]

@Luke_C Thank you so much. For some reason the first one is not working for my workflow BUT the RegEx tool worked.