unformatting text

[Lougee]

SSN field formatted to automatically insert hyphens in the correct places -- xxx-xx-xxxx -- so I don't have to stop twice in every entry to find that darned hyphen key.  Works flawlessly.  However, when someone else entered ID data, she typed in the hyphens so I have some records that, when formatted, look like xxx--x-xxxx.   (The calculation is:  Left (SSN; 3) & "-" & middle (SSN; 4; 2) & "-" & right (SSN; 4).)

 

Is there someway I can see the original -- unformatted, just numbers -- SSN that I typed in?

 

 

[Raybaudi]

You can change the calculation to take account of the user who writes the dashes.

Try:

Let(
d = Filter ( SSN ; 1234567890 ) ;
Left ( d ; 3 ) & "-" & Middle ( d ; 4 ; 2 ) & "-" & Right ( d ; 4 )
)

You can use the same Filter( ) function to obtain unformatted SSN

[Lougee]

I knew I could rewrite the calculation to account for the hyphen and prevent future problems.  I did not realize that would 'reverse engineer' the incorrectly formatted records.  So simple.  Thank you.

[Rick Whitelaw]

I had always thought the proper format for SSN was xxx xx xxxx. Without the hyphens. In Canada S.I.N. format is xxx xxx xxx with no hyphens. Does the government require hyphens on tax forms etc in the US?

[Lee Smith]

TurboTax put the hyphens in my 1040 as I entered the different parts into the work sheet.