MCONCAT with hyphenated numbers and a comma to separate

[LoneScrunger]

I'm trying to display hyphenated numbers in a range (e.g. 001-001-001 as one cell's contents) concatenated with a comma in between except the last number can't have a comma following it. I have a vertical array of these hyphenated numbers (Assessor Parcel Numbers that often start with a zero) and they are concatenated into one cell that is linked to a word document, so they can't end with a comma, however, I would like to include the whole range in the formula so that even if I have 1 or twenty APNs it will end without a comma on the last number. I know that if I designate the exact range each time then MCONCAT will not include a comma, but I don't know how to make it ignore all the blank cells.

I'm using =MCONCAT(AY146:AY174,", ") right now (that is the whole range).

 

[pgc01]

Hi
Welcome to the board

Instead of concatenating them with a comma, concatenate them with a space. This way you can use Trim() to get rid of multiple contiguous spaces and spaces at the beginning or at the end. You then just have to replace the spaces with the separator you want.

For ex., try:

=SUBSTITUTE(TRIM(MCONCAT(AY146:AY174," ") )," ",", ")

 

[LoneScrunger]

Fantastic! Use trim to to remove the very last space and then apply the substitution. For a moment I was thinking that trim would remove all the spaces before and after each APN but after a little head scratching I see what is going on in the formula.

Thanks very much PGC! I've never used either of those functions before.

 

[pgc01]

You're welcome. Thanks for the feedback.