Add number in the text

[sachin483]

required formula or macro for joining column2 with column 1


1.if + sign is not there then join as it is with _


2. if + sign is there then join after first + sign length is not fix

[TABLE="width: 541"]
<tbody>[TR]
[TD]column 1[/TD]
[TD]column 2[/TD]
[TD]required[/TD]
[/TR]
[TR]
[TD]AAAAAAA[/TD]
[TD]6[/TD]
[TD]AAAAAAA_6[/TD]
[/TR]
[TR]
[TD]AAAAAAA+BBBBBBBBBBBB[/TD]
[TD]2[/TD]
[TD]AAAAAAA_2+BBBBBBBBBBBB[/TD]
[/TR]
[TR]
[TD]AAAAAAA+HHHHHH[/TD]
[TD]3[/TD]
[TD]AAAAAAA_3+HHHHHH[/TD]
[/TR]
[TR]
[TD]DDDDD[/TD]
[TD]10[/TD]
[TD]DDDDD_10[/TD]
[/TR]
[TR]
[TD]DDDDD+GGGGGGG[/TD]
[TD]4[/TD]
[TD]DDDDD_4+GGGGGGG[/TD]
[/TR]
[TR]
[TD]MMMMMM[/TD]
[TD]13[/TD]
[TD]MMMMMM_13[/TD]
[/TR]
[TR]
[TD]TTTTT[/TD]
[TD]1[/TD]
[TD]TTTTT_1[/TD]
[/TR]
[TR]
[TD]HHHHHHHH+KKKKKKKKKK[/TD]
[TD]4[/TD]
[TD]HHHHHHHH_4+KKKKKKKKKK[/TD]
[/TR]
[TR]
[TD]HHHHHHHH+KKKKKKK+NNNNNNN[/TD]
[TD]3[/TD]
[TD]HHHHHHHH_3+KKKKKKK+NNNNNNN[/TD]
[/TR]
[TR]
[TD]HHHHHHHH+WWWWWW[/TD]
[TD]3[/TD]
[TD]HHHHHHHH_3+WWWWWW[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

Give this formula a try...

=LEFT(SUBSTITUTE(A1&"+","+","_"&B1&"+",1),LEN(A1)+LEN(B1)+1)

 

[sachin483]

Thanks a lot working fine