We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question
0

Substitute function in Calc being weird

asked 2020-07-08 02:57:27 +0200

Faradn gravatar image

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

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2020-07-08 03:28:21 +0200

Faradn gravatar image

I fixed it, I just took out the first colon of each term in the SUBSTITUTE function. So each term is " X;" instead of "; X;". I guess they were stepping on each others' toes somehow.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-07-08 02:57:27 +0200

Seen: 55 times

Last updated: Jul 08 '20