google-sheetsgoogle-sheets-formulagoogle-sheets-apigoogle-workspace

remove empty cells, using text data from multiples cells from another sheets (some formula not working)

sheet image

Using Google Sheets, I have this formula that takes text data from multiple cells from another sheets:

='24 KPI Weekly'!Z5&" / "&'24 KPI Weekly'!Z6&" / "&'24 KPI Weekly'!Z7&" / "&'24 KPI Weekly'!Z8&" / "&'24 KPI Weekly'!Z32&" / "&'24 KPI Weekly'!Z33&" / "&'24 KPI Weekly'!Z34&" / "&'24 KPI Weekly'!Z35

It gives this as a result: / / Robert Bourassa / / / 1425 Rue des Mélèzes / / St-Blaise

I would like to remove the empty cell. I tried numerous ways that I could find on the web and none are working.

Among them, I tried with the TEXTJOIN function and added the TRUE function:

=TEXTJOIN(" / ", TRUE, '24 KPI Weekly'!Z5, '24 KPI Weekly'!Z6, '24 KPI Weekly'!Z7, '24 KPI Weekly'!Z8, '24 KPI Weekly'!Z32, '24 KPI Weekly'!Z33, '24 KPI Weekly'!Z34, '24 KPI Weekly'!Z35)

But it is not working. It is not taking in consideration most of the cells from the other sheets (it is staying in black instead of being in colors in the formula. So therefore giving me an ERROR message. See screenshots attached. I tried others ways and it is always giving me the error message. As if those fonction do not work with my google sheets. Can someone please help.

Solution

  • Google Sheets syntax varies depending on location

    Using TextJoin can achieve your desired output. Instead of using a decimal comma notation in between your target range '24 KPI Weekly'!Z5, '24 KPI Weekly'!Z6, use semicolon (;)

    I added a reference Explaining syntax differences in your formulas due to your Google Sheets location, that explains why the comma does not work on your end.

    You can try this:

    =TEXTJOIN("/";TRUE; '24 KPI Weekly'!Z5; '24 KPI Weekly'!Z6; '24 KPI Weekly'!Z7; '24 KPI Weekly'!Z8; '24 KPI Weekly'!Z32; '24 KPI Weekly'!Z33; '24 KPI Weekly'!Z34; '24 KPI Weekly'!Z35; '24 KPI Weekly'!Z59; '24 KPI Weekly'!Z60; '24 KPI Weekly'!Z61; '24 KPI Weekly'!Z62; '24 KPI Weekly'!Z63)

    2nd Row:

    =TEXTJOIN(" / ";TRUE; '24 KPI Weekly'!Z11; '24 KPI Weekly'!Z12; '24 KPI Weekly'!Z13; '24 KPI Weekly'!Z14; '24 KPI Weekly'!Z38; '24 KPI Weekly'!Z39; '24 KPI Weekly'!Z40; '24 KPI Weekly'!Z41; '24 KPI Weekly'!Z66; '24 KPI Weekly'!Z67; '24 KPI Weekly'!Z68; '24 KPI Weekly'!Z69; '24 KPI Weekly'!Z70)

    3rd Row:

    =TEXTJOIN(" / "; true; '24 KPI Weekly'!Z17; '24 KPI Weekly'!Z18; '24 KPI Weekly'!Z19; '24 KPI Weekly'!Z20; '24 KPI Weekly'!Z44; '24 KPI Weekly'!Z45; '24 KPI Weekly'!Z46; '24 KPI Weekly'!Z47; '24 KPI Weekly'!Z73; '24 KPI Weekly'!Z74; '24 KPI Weekly'!Z75; '24 KPI Weekly'!Z76; '24 KPI Weekly'!Z77)

    4th row:

    =TEXTJOIN(" / "; true; '24 KPI Weekly'!Z23; '24 KPI Weekly'!Z24; '24 KPI Weekly'!Z25; '24 KPI Weekly'!Z26; '24 KPI Weekly'!Z50; '24 KPI Weekly'!Z51; '24 KPI Weekly'!Z52; '24 KPI Weekly'!Z53; '24 KPI Weekly'!Z80; '24 KPI Weekly'!Z81; '24 KPI Weekly'!Z82; '24 KPI Weekly'!Z83; '24 KPI Weekly'!Z84)

    Sample Output:

    Output1

    Output2

    Output3

    Output4

    Reference:

    TextJoin

    Explaining syntax differences in your formulas due to your Google Sheets location