How do you keep leading 0s in other columns using them?-CodePudding

I have three columns,two are used to calculate the one. I'm using leading 0s for the "waypoint #" and I would like to include them so the Ref # column, its formula is =IF(OR([@[GPS colour]] = "O"), "1", "2")& [@[waypoint '#]].

So the Ref # cells would come out as

CodePudding user response：

One needs to use TEXT to keep the format:

TEXT([@[waypoint '#]],"000")

So:

=IF(OR([@[GPS colour]] = "O"), "1", "2")& TEXT([@[waypoint '#]],"000")

Also it can be simplified:

((@[GPS colour]] <> "O") 1)&TEXT([@[waypoint '#]],"000")

CodePudding user response：

To me GPS color looks like a character (O) not a zero (0).

In case there is more than one Character that should be mapped, try this:

=IFNA(INDEX(configMapping[Mapping],MATCH([@[GPS colour]],configMapping[GPS color],0)),fallback) & TEXT([@[waypoint '#]],"000")

where the mapping table is named configMapping and the cell I4 is called fallback