spaces after data, how to remove it?

[3nduranc3]

Hey all!

I have a little problem while trying to use CONCATENATE. Namely. in the first row after data cells, I have random spaces. So after using the function they look like this.

Example:

abcdfe ______ abbbfff
abcdfe ___oilpo
freterezlzo_____________________ loaidf

So the spaces are in the first cell, is there a function I can remove them? all formats are text. And I would like to have only 1 space after concatenate between the two datas.

Thank you very much!

Ambrus

 

[acerrhod]

try to modified your formula in concatenate:
=CONCATENATE(TRIM(A1),TRIM(B1))
this will remove trailing spaces in either A1 & B1

 

[Snakehips]

Ambrus,

Eg If your formula is =A1&A2

Then try =TRIM(A1)&TRIM(A2)

Hope that helps.

 

[3nduranc3]

Thanks!

Neve knew there was a TRIM function :O
Cool!

Thanks again!

 

[KrazyAl]

Hi

You can also use the "Clean" function to remove any non printable characters. Then add the Substitute to remove carriage returns. Yes you can use all of them in the same formula.

=TRIM(CLEAN(SUBSTITUTE(A1,CHAR(13),"")))

 

[KrazyAl]

*