simplifying conversion of Week/Day/Segment ID with leading zeroes

[jaybushman]

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!

[Alexey_Gusev · Accepted solution]

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)

[jaybushman]

this worked perfectly, thanks a lot!