Input value if else

[Istwineres]

Hi, I've a column with social security number where if the value is not present the info provider could write: 0000000000000000  (range of zeros) till "" (empty) as string, I was wondering if there's a better way than if ssn="0000000000000000" or ssn="000000000000000" or ... until or ssn="" to handle this situation. thanks

[Luke_C]

@Istwineres 

I'm not sure I fully understand the ask. Are you looking to replace empty values or identify instances where the provider put all 0s? SSNs are usually 9 digits so if they're sending you that many 0s you can just use a length function to identify records that exceed the usual data (assuming the data is clean). 

If possible attaching sample data and expected results might help the community. 

[mceleavey]

Hi @Istwineres ,

can you post some mock data representing what you're trying to along with the records that don't have "000000000", and we can build it for you.

Thanks.

M.

[Jon_Taylor]

Hi, A few Questions that may help bring a solution.

Are you trying to use an if formula to say if a row in the column is blank then give a SSN as all zeros?

Are all SSNs you have the same number of digits or string characters say all SSNs are 7 numbers xxx-xxx-xxxx.or xxxxxxx

             if this is the case i think in an "if than formula you can have it give 7 - 0's instead of 1 null 0.

Are you asking if a SSN is incomplete 12x-123-1234 shows as 120-123-1234?

[Istwineres]

Hi, the input value could like this:

ssn = "0000000000000000"

ssn = "000000000000000"

ssn = "00000000000000"

ssn = "0000000000000"

ssn = "000000000000"

ssn = "00000000000"

ssn = "0000000000"

.

.

.

.

ssn = "0"

if those are the input values I'd like to replace with an empty string. Thanks!

[Luke_C · Accepted solution]

@Istwineres 

You can try regex: \<0+\> that checks if the record is all 0s. See attached, I ran a few different checks, but of course validate against your data.

[bpatel]

hi @Istwineres 

you could also change the data type from a string to a number and do a if than statement like this

hope this helps

[AdamR_AYX · Accepted solution]

Another option is

iif(ReplaceChar([ssn], "0", "")=="", "", [ssn])

Attached an example

[estherb47]

Hi @Istwineres 

Looks like you have at least two solutions here that will work. I've gone ahead and marked them as such so that when others have a similar question, they'll get to an answer.

If you'd like, please mark other solutions that work as well.

Cheers,

Esther