Hi all:
I'm trying to simplify the process of converting a short ID code to a longer ID code inserting leading zeroes where needed. The ID codes are structured to track Week, Day, and Segment numbers
For example, if the input data is 1.2.3 (week 1, day 2, segment 3), the necessary output needs to be formatted W01.D01.S03
The wrinkle is that while day and segment are always going to be a single digit, the week value could either be one digit or two digits.
I've managed to get it working with a series of breakouts that takes the input field, breaks out the week, day, and segment into separate field, adds leading zeroes where necessary, then concatenates them back into the final output id. But it takes 8 separate steps/unique fields to accomplish. I'm looking for a way to do it in fewer steps.
Here's how I have it working currently:
Input: "ShortID" (e.g. 1.2.3, 12.7.1) -- manually entered
1) "BreakoutWeek"
LEFT(ShortID, FIND(".", ShortID) -1)
2) "BreakoutWeek0"
IF(
BreakoutWeek,
CONCATENATE(
REPT(
"0",
2 - LEN({BreakoutWeek} & "")
),
{BreakoutWeek}))
3) "BreakoutDay.Seg"
RIGHT(ShortID, (LEN(ShortID) - FIND(".", ShortID)))
4) "BreakoutDay"
LEFT({BreakoutDay.Seg}, FIND(".", {BreakoutDay.Seg}) -1)
5) "BreakoutDay0"
IF(
BreakoutDay,
CONCATENATE(
REPT(
"0",
2 - LEN(BreakoutDay & "")
),
BreakoutDay))
6) "BreakoutSeg"
RIGHT({BreakoutDay.Seg}, (LEN({BreakoutDay.Seg}) - FIND(".", {BreakoutDay.Seg})))
7) "BreakoutSeg0"
IF(
BreakoutSeg,
CONCATENATE(
REPT(
"0",
2 - LEN(BreakoutSeg & "")
),
BreakoutSeg))
8 ) "FullID"
CONCATENATE(""W",{BreakoutWeek(0)},".D",{BreakoutDay(0)},".S",{BreakoutSeg(0)})
This process works, and results in:
ShortID: 1.2.3 --> FullID: W01.D02.S03
ShortID: 12.7.1 --> FullID: W12.D07.S03
But is there a way to do all of this in a single formula? Or at least fewer breakout fields? Thanks!
