Two Substitute Formulas in one cell

[roxdrob]

I need the below to read White, Blue, Red & Silver. I have used the =SUBSTITUTE(B4," &", ",",1) to remove the first one, but I can't figure out how to remove the second &. Please help. TIA

<colgroup><col width="64"><col width="203"></colgroup><tbody>
[TD="width: 64"]ADJ4
[/TD]
[TD="width: 203"]White & Blue & Red & Silver
[/TD]

</tbody>

 

[jtakw]

Hi,

Since you only gave 1 sample...if it's Always 4 words separated by commas, then use this:

Book1
	A	B
1	White & Blue & Red & Silver	White, Blue, Red & Silver
Sheet705

Cell Formulas
Range	Formula
B1	=SUBSTITUTE(SUBSTITUTE(A1," &",","),","," &",3)

 

[roxdrob]

Thanks a bunch. I tried a version of that and it wasn't working for me. Is that saying delete all but the 3rd &?

I do have other combos, like 1 & 2 "&"s, but I figured them out and was going to do an if/then formula to calculate them. Unless you know of an easier way. I'm having to do it in multiple steps.

 

[Fluff]

How about

Book1
	A	B
10	White & Blue & Red & Silver	White, Blue, Red & Silver
11	Blue & Red & Silver	Blue, Red & Silver
12	Red & Silver	Red & Silver
Sheet1

Cell Formulas
Range	Formula
B10	=SUBSTITUTE(SUBSTITUTE(A10," &",","),","," &",LEN(A10)-LEN(SUBSTITUTE(A10,"&","")))

 

[jtakw]

B3 formula will work for any number of words:

Book1
	A	B
1	White & Blue & Red & Silver	White, Blue, Red & Silver
2
3	White	White
4	White & Blue	White & Blue
5	White & Blue & Red	White, Blue & Red
6	White & Blue & Red & Silver	White, Blue, Red & Silver
7	White & Blue & Red & Silver & Black	White, Blue, Red, Silver & Black
Sheet705

Cell Formulas
Range	Formula
B1	=SUBSTITUTE(SUBSTITUTE(A1," &",","),","," &",3)
B3	=IFERROR(SUBSTITUTE(SUBSTITUTE(A3," &",","),","," &",LEN(A3)-LEN(SUBSTITUTE(A3,"&",""))),A3)

 

[roxdrob]

Perfect! Thanks so much for your help!

 

[jtakw]

You're welcome, thanks for the feedback.

I'd like to make an improvement to my B3 formula in Post # 5 as follows:

Book1
	A	B
3	White	White
4	White & Blue	White & Blue
5	White & Blue & Red	White, Blue & Red
6	White & Blue & Red & Silver	White, Blue, Red & Silver
7	White & Blue & Red & Silver & Black	White, Blue, Red, Silver & Black
Sheet705

Cell Formulas
Range	Formula
B3	=SUBSTITUTE(SUBSTITUTE(A3," &",","),","," &",MAX(1,LEN(A3)-LEN(SUBSTITUTE(A3,"&",""))))

 

[roxdrob]

Thanks so much!