Immediate Help Needed - Excel Newbie **remove last comma in sum

[jamesdor45]

Hi Guys!

Great board and the answers I found here helped me on many occasions.

I’m an excel newbie and I have a stupid question that unfortunately I couldn’t make or solve, and I really need your help here.

I need to make a simple formula that will remove the last comma(s). For example, I have 7 lines (B1 – B7) that I insert a text into those. I then have another line (B8) that will show all of this words (including the commas). However I wont always have to insert all 7 options, so in some scenarios where I have to fill only two lines (B1 and B2) then I will get all of the commas from all other lines (B3 – B7).

The formula I currently use for A8 is this:


=B1&","&B2&","&B3&","&B4&","&B5&","&B6&","&B7


Can you guys please help me here? I really need this find a solution for this as soon as possible.

Thanks again for your time!

Regards,
Jerry

 

[Weazel]

you could try....

=SUBSTITUTE(TRIM(B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7)," ",", ")

 

[Rick Rothstein]

you could try....

=SUBSTITUTE(TRIM(B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7)," ",", ")

What if the text in one or more of the cell contains its own spaces... won't they (incorrectly) become commas?

 

[paddydive]

Right Rick, I was about to ask that only...

Can we do it with sumproduct or an array formula.... no sure how

 

[jamesdor45]

Hi Weazel,

Thank you very much for the quick follow-up!

It works perfect expect for that fact that I forgot to mention that some lines may include 2 ore more words, and currently according to this formula a comma is being added between them. Let me explain it better using a live example:

For example I insert the following:

B1 - Dog
B2 - Big Dog
B3 – Cat
B4 – Small Cat
B5
B6
B7

And on B8 I use this formula that you have written:

=SUBSTITUTE(TRIM(B1&" "&B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7)," ",",")

which looks like this on the sheet:

Dog,Big,Dog,Cat,Small,Cat

BUT, I need it to look like this:

Dog,Big Dog,Cat,Small Cat

*btw I didnt need any extra spacing after each words ends, so I deleted the last “,”.

Thanks again for your time!

Regards,
Jerry

 

[Weazel]

Well, that's a great point, I didn't take that into consideration.

Unfortunately I'm stumped as far as a formula approach, but you could use Rick's Flexible Concatenation Function which is fantastic and works great on the 2 word dilemma and the trailing comma issue

 

[Rick Rothstein]

I need to make a simple formula that will remove the last comma(s). For example, I have 7 lines (B1 – B7) that I insert a text into those. I then have another line (B8) that will show all of this words (including the commas). However I wont always have to insert all 7 options, so in some scenarios where I have to fill only two lines (B1 and B2) then I will get all of the commas from all other lines (B3 – B7).

There is always this rather ugly-looking formula...

=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(B1," ","|")&" "&SUBSTITUTE(B2," ","|")&" "&SUBSTITUTE(B3," ","|")&" "&SUBSTITUTE(B4," ","|")&" "&SUBSTITUTE(B5," ","|")&" "&SUBSTITUTE(B6," ","|")&" "&SUBSTITUTE(B7," ","|"))," ",","),"|"," ")

 

[Rick Rothstein]

I need to make a simple formula that will remove the last comma(s). For example, I have 7 lines (B1 – B7) that I insert a text into those. I then have another line (B8) that will show all of this words (including the commas). However I wont always have to insert all 7 options, so in some scenarios where I have to fill only two lines (B1 and B2) then I will get all of the commas from all other lines (B3 – B7).

There is always this rather ugly-looking formula...

=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(B1," ","|")&" "&SUBSTITUTE(B2," ","|")&" "&SUBSTITUTE(B3," ","|")&" "&SUBSTITUTE(B4," ","|")&" "&SUBSTITUTE(B5," ","|")&" "&SUBSTITUTE(B6," ","|")&" "&SUBSTITUTE(B7," ","|"))," ",","),"|"," ")

 

[jamesdor45]

Thank you very much for your time.

If anyone can think of a solution without using the VB editor that will be ideal.

Regards,
Jerry

 

[pjmorris]

Hi Jerry,

This solution doesn't use the VB editor just copy this into cell B8: =IF(B1<>"",B1,"")&IF(B2<>"",", "&B2,"")&IF(B3<>"",", "&B3,"")&IF(B4<>"",", "&B4,"")&IF(B5<>"",", "&B5,"")&IF(B6<>"",", "&B6,"")&IF(B7<>"",", "&B7,"")

The only thing to note is that you do need to have something in Cell B1 otherwise the result will start with a ", "

Hope this helps.

Regards