Changing Data Format

[DataAnalyst]

I have IDs which need to be formatted as per the state code.

Below are 3 scenarios.

1.If State Code is 04 then format 123456789 as  123456789

2.If State Code is 05 then format 123-456789

3.If State Code is 06 then format 1234567.89

Thank you!

[JohnJPS]

A formula tool with the following would work:

Switch([STATE CODE],"unknown",

"01",ToString([ID NUMBER]),

"02",Left(ToString([ID NUMBER]),3) + "-" +
     Right(ToString([ID NUMBER]),Length(ToString([ID NUMBER]))-3),

"03",Left(ToString([ID NUMBER]),Length(ToString([ID NUMBER]))-2) + "." + 
     Right(ToString([ID NUMBER]),2)

)

This basically does a custom format of your choice for each state code, using the "Switch" statement.... (I have 01/02/03... you would have 04/05/06).  There are many ways to achieve the formatting; this is just one example.  Hope that helps.

[MarqueeCrew]

@DataAnalyst

I haven't looked at @JohnJPS's solution, but I do have an answer for you.  In my solution I check to see if the ID has 9 digits, if it does, I output formulas for 04, 05, 06 codes.  If another state code is used or the length isn't 9 digits, I output "error".

Thanks,

Mark

[MarqueeCrew · Accepted solution]

@JohnJPS,

It's comforting to see similar trains of thought and how unique our solutions can be.  Ideally, we would know which format is the most common and what types of error handling is required for the workflow.  If we can count on a 9-digit value, the modification that I would make to the logic would be to use Left(ID,3)+'-'+Right(ID,6) as an example (not needing to calculate the length of 6).

I'm a fan of @jdunkerley79 and his use of regular expressions.  I tend to answer with them too now.  I'm sure that @DataAnalyst will be happy with either of our solutions as they point him in the direction of his request, but also go a bit beyond to his need.

Cheers,

Mark

[jdunkerley79]

As @MarqueeCrew mentioned my Regex habits...

A Regex look up:

Match and then format :)

IIF(REGEX_Match([ID NUMBER],[Match]),
REGEX_Replace([ID NUMBER],[Match],[Replace])
,NULL())

[DataAnalyst]

Thanks MarqueeCrew! It worked. 

[DataAnalyst]

Thanks  jdunkerley79

You guys are awesome!!