Why are there extra spaces

Here is one way to approach concatenation when sometimes you have a value in a cell and sometimes you don't.

The words are in columns A through C

The working formula, in H2 filled down, is:

=TRIM(SUBSTITUTE(SUBSTITUTE(A2&"|"&B2&"|"&C2,"||"," "),"|"," "))

That is all you need.

The formulas in the Step 1 through Step 4 columns are there only to illustrate how the formula works.

Step 1: In D2, filled down, is the formula =A2&"|"&B2&"|"&C2. All that does is put a | between whatever it finds in the cells, including blanks. (You can use an uncommonly used character other than | if you would prefer that).

Step 2: In E2, filled down, is =SUBSTITUTE(D2,"||"," "). That replaces any "double" | it finds with a space.

Step 3: In F2, filled down, is =SUBSTITUTE(E2,"|"," "). That replace any | it finds with a space.

Step 4: In G2, filled down, is =TRIM(F2). That removes any leading or trailing spaces.

The formula above works for three words only. To extend it to more words all you have to do is add more cells to Step 1.

For example, for four words in A2 through D2, you would use this formula:

=TRIM(SUBSTITUTE(SUBSTITUTE(A2&"|"&B2&"|"&C2&"|"&D2,"||"," "),"|"," "))

And so on...

SG

Actually, I tried TRIM() last, Barry.

I tried your method and in Numbers 10.0 I don't think you need that extra TRIM.

So for four words in A2 through D2:

=TRIM(SUBSTITUTE(SUBSTITUTE(A2&"|"&B2&"|"&C2,"||"," "),"|"," "))

Or:

=TRIM(CONCATENATE(A2," ",B2," ",C2," ",D2))

Or even shorter:

=TRIM(A2&" "&B2&" "&C2&" "&D2)

SG

Hi SG,

"I tried your method and in Numbers 10.0 I don't think you need that extra TRIM.

=TRIM(CONCATENATE(A2," ",B2," ",C2," ",D2))

Or even shorter:

=TRIM(A2&" "&B2&" "&C2&" "&D2)

Interesting. I included a space after the 'last' word for consistency in cases where the 'last' word in the result wasn't the last word in the table. I wonder is that was what caused the need for the extra TRIM. In my single TRIM version the 'extra' internal spaces didn't get trimmed, leaving a huge gap in the examples in A8 and A9.

Regards,

Barry

As you've discovered this kind of concatenation by formula quickly gets complicated if sometimes you have a value in a cell and sometimes you don't. You mention you are dealing with more than three words. Do you have an example?

SG

I was curious as to why SG included SUBSTITUTE and the extra step in the formula.

I found out that doing a single CONCATENATE and TRIMming the result, which I expect SG did first, doesn't strip the internal spaces (as I'd expect it to).

Some time later, I had a further thought on it and returned to the issue.

The formula below seems to also work with more than thre words:

Columns I, J and K are there to let me know the information while developing the formula, and aren't needed in the solution.

I: total count of characters in the words in columns B to H

J: total count of characters (including spaces) in the result in column A, plus 1. This count should agree with the number of words in the result.

Without the second TRIM, the result kept all internal spaces added by CONCATENATE. with it, multiple spaces between the words were trimmed of the extras, leaving a single space between words, as can be seen in the image below of the formula results, pasted into a text box in Pages, and set to Show invisibles.

Regards,

Barry

Hi SG,

Familiar description. Here's the Notes from the iWork '09 Formulas and functions User guide:

Notes

• TRIM removes all spaces before the first character, all spaces after the last character, and all duplicate spaces between characters, leaving only single spaces between words.

The rest of the article is also pretty much word-for-word the same as shown in your post.

Memory says I used it a few times in that version, which is likely why I was surprised by the 'still has multiple spaces' result I got with a single TRIM when testing it for this question.

Haven't deleted the table for this question yet, so I my get back to it and run another test with a single TRIM.

Regards,

Barry

That's a completely different problem. Suggest you start a new thread and include specifics on how you want the output, all within a cell, in different cells, etc.

SG

Hi Barry,

I'm not sure. It may be something that was cleaned up in Numbers since the version you are running?

I can confirm the all three formulas, the one with SUBSTITUTE() and the two with a single TRIM(), produce the desired results in Numbers 10.0 and Excel for Mac 16.37.

I had always assumed that TRIM() just removed blanks before and after a string.

I just now looked it up TRIM() at Help > Formulas and Functions Help in the menu:

Wouldn't have found that out without seeing INDY-GO's post and testing your formula. Learn something every day!

SG