Search code examples
arraysgoogle-sheetsexcel-formulagoogle-sheets-formulaarray-formulas

How to remove extra spaces between words in excel or google sheet?

I want to remove spaces from the words. e.g. Tube and Light should be Tubeandlight. Also i need it to be an arrayformula and concatenate H,E and F columns.

Using Google sheet / excel. I guess the formula should work both on google sheet and excel.

Here is the result of the formula =arrayformula(if((H4:H)="","",trim(E4:E)&"-"&trim(F4:F)&"-"&trim(H4:H))) .Tube and light-blue and green-120 meter. It does trim but i am not able to remove the spaces between the words

How can i remove the text between the words? Thanks

Solution

  • try:

    =ARRAYFORMULA(SUBSTITUTE(IF(H4:H="",,E4:E&"-"&F4:F&"-"&H4:H), " ", ))