Help with Substitute on Multiple variable

[tbruce]

Hi,

I'm trying to write a formula that will replace all the 3's with 4, all the 2's with 3, and all the 1's with 2, but need to do it in this order so it does the 3s first, then the 2s, then the 1s, that way it doesn't apply it twice to the number. The digit is in the first position from the left (e.g. 13029344, 23029344,33029344). Is there a way to do this with a formula? Any help would be appreciated.

Here is what I have for the first change which works, but can't figure out how to get the second and third variables changed with the same formula =SUBSTITUTE(B2,"3","4",1)

 

[kweaver]

Are you wanting to replace ALL matching digits or just the first (left-most) digit?
Does B2 contain: 13029344 and you want the leading replaced with a 2 to get 23029344 ? Is B2 numeric or alphanumeric?

If not, could you provide a specific example or three of what's in B2 and what you ultimately want?

 

[mehidy1437]

Try below,

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,3,4),2,3),1,2)

 

[kweaver]

If mehidy's solution is what you want, I'd suggest placing 1* after the = sign to get a numeric result if you want a numeric result.

 

[tbruce]

This at first looks to work, but for some reason this is also changing the 3rd digit from the left when it is 1,2,3. The only digit I want to change in in the 1st position from the left.

 

[kweaver]

try:

=1*SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,3,4,1),2,3,1),1,2,1)

 

[tbruce]

Yea that didn't work either - it's still changing the #3 digit. 30139 is being changed to 40239, so it did the first digit but also the 3rd digit too since it was a 1.

 

[tbruce]

Looks like the ,1 is replacing the first instance of the number versus in the first position of the value.

 

[mehidy1437]

See if this is, what you are looking for or not?

Book1
	A	B	C	D
1	30139			40139
2	20139			30139
3	10139			20139
4	30139			40139
5	30139			40139
Sheet1

Cell Formulas
Range	Formula
D1:D5	D1	=IF(LEFT(A1,1)="3",SUBSTITUTE(A1,3,4,1),IF(LEFT(A1,1)="2",SUBSTITUTE(A1,2,3,1),IF(LEFT(A1,1)="1",SUBSTITUTE(A1,1,2,1),"")))

 

[Peter_SSs]

Perhaps this?

20 09 09.xlsm
	A	B
1	30139	40139
2	20139	30139
3	10139	20139
4	30139	40139
5	30139	40139
6	55555	55555
Substitute

Cell Formulas
Range	Formula
B1:B6	B1	=--REPLACE(A1,1,1,LEFT(A1,1)+OR(--LEFT(A1,1)={1,2,3}))