Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Mar 12, 2023 11:00 AM - edited Mar 12, 2023 11:02 AM
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!
Solved! Go to Solution.
Mar 13, 2023 06:31 PM
Hi,
I suppose you don't need to check for extra spaces, otherwise use TRIM(Field) instead of Field
SUBSTITUTE(
REPLACE(Field,1,FIND('.',Field),'W'&
IF(FIND('.',Field)<3,'0') &
LEFT(Field,FIND('.',Field)-1)&'.D0'),
'.','.S0',2)
Mar 13, 2023 06:31 PM
Hi,
I suppose you don't need to check for extra spaces, otherwise use TRIM(Field) instead of Field
SUBSTITUTE(
REPLACE(Field,1,FIND('.',Field),'W'&
IF(FIND('.',Field)<3,'0') &
LEFT(Field,FIND('.',Field)-1)&'.D0'),
'.','.S0',2)
Mar 14, 2023 05:17 PM
this worked perfectly, thanks a lot!