SSN Format

building reports webintelligence
1

Hey BOB Members, I’m trying to find formatting a social security number from 123456789 to format 123-45-6789.

I had return Subtring
=Substr(FormatNumber([Social Secutity Number - Full];“0”);1;3) + “-” + Substr(FormatNumber([Social Secutity Number - Full];“0”);4;2)+ “-” + Substr(FormatNumber([Social Secutity Number - Full];“0”);6;4).

It’s working but issue is someone SSN starting with 0. giving me problem
(i.e) 012345678 showing 123-45-678.

can any one suggest

Thanks
Janho

janho (BOB member since 2011-07-28)

2

Probably my logic is silly.
But you can check the length of the final SSN in the string format. If is it not full, then you can prefix a zero. There could be better & optimal solutions which I am not able to think.

Jansi :india: (BOB member since 2008-05-12)

3

I assume that SSN is held as a number, and so those withs leading a leading 0 appear as an 8-digit figure.

So, do a length test of [SSN]: if length is = 8 then concatenate with a “0” (otherwise keep the [SSN] value).

you may need to do a bit of nesting if there is chance that a SSN could have two leading 00s.

mpwalker :uk: (BOB member since 2008-11-06)

4 
=Replace(FormatNumber([SSN];"000 000 000");" ";"-")
5

=Substr(right(‘000000000’ +([Social Security Number-Full];“9”); 1; 3) + “-” +Substr(right(‘000000000’ +(FormatNumber([Social Security Number -Full];“9”); 4; 2) + “-” +Substr(right(‘000000000’ +(FormatNumber([Social Security Number-Full] ;“9”); 6; 4))))))

I’m trying in this way. can any tell me whats wrong in this subtring…

janho (BOB member since 2011-07-28)

6

Thanks Mark . It’s Working .

janho (BOB member since 2011-07-28)

7

Using the following formula, I get an error stating that the “FormatNumber function uses an invalid data type”.

=Replace(FormatNumber([Borrower 1 SSN];“000 000 000”);" “;”-")

I’m assuming the [Borrower 1 SSN] field I’m trying to convert is not a number field? Any ideas?

csmwhite (BOB member since 2016-11-23)

8

Sounds like it.

If Borrower 1 SSN is already in the format 000 000 000 then simply remove the FormatNumber part of your formula.

If Borrower 1 SSN is in the format 000000000 then use this:

=Substring([Borrower 1 SSN];1;3)+"-"+Substring([Borrower 1 SSN];4;3)+"-"+Substring([Borrower 1 SSN];7;3)
9

That was pretty close Mark! I ended up using the following:
=Substr([Borrower 1 SSN];1;3)+"-"+Substr([Borrower 1 SSN];4;2)+"-"+Substr([Borrower 1 SSN];6;4)

Thanks for the assist!

I also found another method this morning:
=Left([Borrower 1 SSN];3) + “-” + Substr([Borrower 1 SSN];4;2)+ “-” + Right([Borrower 1 SSN];4)

csmwhite (BOB member since 2016-11-23)

10

Great stuff, thanks for letting us know it worked. :slight_smile: