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

Question

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

`=IF(OR([@[GPS colour]] = "O"), "1", "2")& TEXT([@[waypoint '#]],"000")` — Scott Craner, Apr 29 '22 at 14:39
or: `((@[GPS colour]] <> "O")+1)&TEXT([@[waypoint '#]],"000")` just a little bit shorter. — Scott Craner, Apr 29 '22 at 14:42

score 2 · Answer 1 · answered Apr 29 '22 at 15:45

2

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")

answered Apr 29 '22 at 15:45

Scott Craner

148,073
10
49
81

score -1 · Answer 2 · answered Apr 29 '22 at 15:23

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

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

2 Answers2