I am trying to convert lists of one and two-digit numbers into all two-digit numbers, so “1” would be “01” and so on. I tried adding leading zeroes, but for some reason it doesn’t work when I entered the list, which was separated by colons. I get the feeling that even if I got that to work, it would turn my two digit numbers into three digit numbers, which I don’t want (“012” and so on).
The other thing I tried was a nested SUBSTITUTE function:
SUBSTITUTE(SUBSTITUTE(Location,Search1,Replace1),Search2,Replace2))
This was fine until there were adjacent numbers that were the same. For instance “; 7; 10; 8; 9; 5; 8; 14; 5; 5; 13;” returns “; 07; 10; 08; 09; 05; 08; 14; 05; 5; 13;”. Notice that the lone 5 and the first 5 in the adjacent pair are corrected, but not the second in the pair.
Here is the exact formula I am using:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A$1,"; 1;","; 01;"),"; 2;","; 02;"),"; 3;","; 03;"),"; 4;","; 04;"),"; 5;","; 05;"),"; 6;","; 06;"),"; 7;","; 07;"),"; 8;","; 08;"),"; 9;","; 09;")