need to replace 3 digits if condition met

[CRAIG20]

Hi. I would like a formula


I have 2 rows of cells A&B. A contains a long number and B a reference.

Eg. If the cell in B4 says "ALL" I would like to replace the last 4 digits in A4 with "1234" If B4 is blank then it leaves A4 as it is.

Thanks in adavance.

 

[Peter_SSs]

Please be careful with your descriptions.
- Thread title says replace 3 but post says replace 4
- "rows of cells A&B". A & B are columns, 1 & 2 are rows.

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)


See if this is what you want

20 10 19.xlsm
	A	B	C
1
2	123456789	ALL	123451234
3	986541	ALL	981234
4	965321458965		965321458965
Replace digits

Cell Formulas
Range	Formula
C2:C4	C2	=IF(B2="",A2,--(LEFT(A2,LEN(A2)-4)&1234))

 

[CRAIG20]

Please be careful with your descriptions.
- Thread title says replace 3 but post says replace 4
- "rows of cells A&B". A & B are columns, 1 & 2 are rows.

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)


See if this is what you want

20 10 19.xlsm
	A	B	C
1
2	123456789	ALL	123451234
3	986541	ALL	981234
4	965321458965		965321458965
Replace digits

Cell Formulas
Range	Formula
C2:C4	C2	=IF(B2="",A2,--(LEFT(A2,LEN(A2)-4)&1234))

Thank you and apologies, I should have been clearer. Just one more thing. If I want to replace the first 3 of the last 4 digits but leave the 4th one. eg. replace 2224 with 3334? full length of digits is 18. can you let me know this formula?

Thanks again

 

[Peter_SSs]

full length of digits is 18

If they are all 18 digits then try this

20 10 19.xlsm
	A	B	C
1
2	123456789123456789	ALL	123456789123453339
3	123456789123456789		123456789123456789
Replace digits

Cell Formulas
Range	Formula
C2:C3	C2	=IF(B2="",A2,REPLACE(A2,15,3,333))

 

[CRAIG20]

If they are all 18 digits then try this

20 10 19.xlsm
	A	B	C
1
2	123456789123456789	ALL	123456789123453339
3	123456789123456789		123456789123456789
Replace digits

Cell Formulas
Range	Formula
C2:C3	C2	=IF(B2="",A2,REPLACE(A2,15,3,333))

That's perfect. Thank you very much for your help.

 

[Peter_SSs]

That's perfect. Thank you very much for your help.

You're welcome. Thanks for the follow-up.