Add Suffix to single cell with values separated by comma

[chingoni]

Hello Excel Gurus,
This is my first post to any forum ever so forgive me if have made any mistake.

I have searched for forum but could not locate something that matches.

I have column with cells having values varies in QTY and separated by comma.
I like to add suffix to each value in cell that separated by comma.

example below with what output should look like.

column a
A,B,C
A,B
H,U,L,P
K1
R1
X12,X13,X18
G1,G7,G9,G14,G19


I like to add suffix"_BD" to each value in cell SO


Output should be like below


column b
A_BD,B_BD,C_BD
A_BD,B_BD
H_BD,U_BD,L_BD,P_BD
K1_BD
R1_BD
X12_BD,X13_BD,X18_BD
G1_BD,G7_BD,G9_BD,G14_BD,G19_BD


Regards,
Chingoni

 

[Michael M]

one way

=SUBSTITUTE(SUBSTITUTE(A1,",","_BD,"),RIGHT(A1,1),RIGHT(A1,1)&"_BD")

 

[chingoni]

Hi Michael,

Thanks for the quick response.

Your solution is perfect and does solve my needs.

Thanks Again.

Regards,
Chingoni

 

[chingoni]

Hi Michael,

I guess I spoke too soon.

Formula seems not to work if character count varies in random order. see sample below. last column is what I expect to see.but Im getting results as in second column.


X44 X4_BD4_BD X44_BD
XYZ,X1,X2,X45,X X_BDYZ_BD,X_BD1_BD,X_BD2_BD,X_BD45_BD,X_BD XYZ_BD,X1_BD,X2_BD,X45_BD,X_BD
R11 R1_BD1_BD R1_BD
R1 R1_BD R1_BD
X1 X1_BD X1_BD
X2,X111,X44 X2_BD,X111_BD,X4_BD4_BD X2_BD,X111_BD,X44_BD
Z12,Z34567,XYTR Z12_BD,Z34567_BD,XYTR_BD Z12_BD,Z34567_BD,XYTR_BD
X11 X1_BD1_BD X11_BD

Regards,
Chingoni

 

[chingoni]

Data	reult	expected
X44	X4_BD4_BD <colgroup><col width="330" style="width:248pt"> </colgroup><tbody> </tbody>	X44_BD <colgroup><col width="330" style="width:248pt"> </colgroup><tbody> </tbody>
XYZ,X1,X2,X45,X	X_BDYZ_BD,X_BD1_BD,X_BD2_BD,X_BD45_BD,X_BD <colgroup><col width="330" style="width:248pt"> </colgroup><tbody> </tbody>	XYZ_BD,X1_BD,X2_BD,X45_BD,X_BD <colgroup><col width="330" style="width:248pt"> </colgroup><tbody> </tbody>
R11	R1_BD1_BD <colgroup><col width="330" style="width:248pt"> </colgroup><tbody> </tbody>	R11_BD <colgroup><col width="330" style="width:248pt"> </colgroup><tbody> </tbody>
R1	R1_BD <colgroup><col width="330" style="width:248pt"> </colgroup><tbody> </tbody>	R1_BD <colgroup><col width="330" style="width:248pt"> </colgroup><tbody> </tbody>
X1	X1_BD <colgroup><col width="330" style="width:248pt"> </colgroup><tbody> </tbody>	X1_BD <colgroup><col width="330" style="width:248pt"> </colgroup><tbody> </tbody>
X2,X111,X44	X2_BD,X111_BD,X4_BD4_BD <colgroup><col width="330" style="width:248pt"> </colgroup><tbody> </tbody>	X2_BD,X111_BD,X44_BD
Z12,Z34567,XYTR	Z12_BD,Z34567_BD,XYTR_BD <colgroup><col width="330" style="width:248pt"> </colgroup><tbody> </tbody>	Z12_BD,Z34567_BD,XYTR_BD <colgroup><col width="330" style="width:248pt"> </colgroup><tbody> </tbody>
X11	X1_BD1_BD <colgroup><col width="330" style="width:248pt"> </colgroup><tbody> </tbody>	X11_BD

<tbody>
</tbody>

Sorry, my post text got wrapped so I added table now. Sorry as I'm complete newbie as this is my first ever forum membership.

Regards,
Chingoni

 

[63falcondude]

Try

=SUBSTITUTE(A2,",","_BD,")&"_BD"

 

[chingoni]

Hi Falcon Dude,

Your solution worked on all variation I had. if there is anything will post back.

Thanks
Chingoni

 

[63falcondude]

Hi Falcon Dude,

Your solution worked on all variation I had. if there is anything will post back.

Thanks
Chingoni

Great! Happy to help.