Help

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

Topic Labels: Formulas
Solved
Jump to Solution
475 2
cancel
Showing results for 
Search instead for 
Did you mean: 
jaybushman
6 - Interface Innovator
6 - Interface Innovator

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!

 

 

 

 

 

1 Solution

Accepted Solutions
Alexey_Gusev
12 - Earth
12 - Earth

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)

See Solution in Thread

2 Replies 2
Alexey_Gusev
12 - Earth
12 - Earth

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
6 - Interface Innovator
6 - Interface Innovator

this worked perfectly, thanks a lot!