Concatenate

Topic

New Reply

Good afternoon

I have not used the Concatenate formula before and I am getting a strange result in cell N11 for example I am typing =Concatenate(B11,” – “,C11) expecting the result to look like SG – IN but instead I get SG in N11 and -IN in P11?

Reply | Quote

Replies

Perhaps column N is too narrow to display the complete result?

Reply | Quote

Good afternoon

I have not used the Concatenate formula before and I am getting a strange result in cell N11 for example I am typing =Concatenate(B11,” – “,C11) expecting the result to look like SG – IN but instead I get SG in N11 and -IN in P11?

In addition to concatenate, you can use “&” syntex.

Reply | Quote

In addition to concatenate, you can use “&” syntex.

Thanks Prasad and Hans

However it still does the same as you can see from the screenshot below with dummy data, this is with both the & and the Concatenate formula?

Editted: helps if I attach the picture!!

Reply | Quote

Thanks Prasad and Hans

However it still does the same as you can see from the screenshot below with dummy data, this is with both the & and the Concatenate formula?

Editted: helps if I attach the picture!!

It seems that cell are merged & formatted in a particular manner. try de-merging cell & format in usual way.

Reply | Quote

It seems that cell are merged & formatted in a particular manner. try de-merging cell & format in usual way.

Hi Prasad

The cells are not split until I type in either of the formulas and it then gives the appearance of splitting the cells but I don’t think they are becaus I can tab between the cells as normal, I even went right across to column AZ which is nowhere near the data but it is still doing the same and taking out the cell lines for 3 or 4 cells?

Reply | Quote

Hi Prasad

The cells are not split until I type in either of the formulas and it then gives the appearance of splitting the cells but I don’t think they are becaus I can tab between the cells as normal, I even went right across to column AZ which is nowhere near the data but it is still doing the same and taking out the cell lines for 3 or 4 cells?

Is it possible to attache a copy of sheet instead of image?

Reply | Quote

Is it possible to attache a copy of sheet instead of image?

Hi John

Column B & C are populated by 2 letter country codes, for example NL, US, UK, DE, IN, SG etc. All I am putting in is either

=Concatenate(B11,” – “,C11) or as Prasad suggested =B11&” – “&C11 and both behave in the same way.

Having looked a bit more it may be that this is a huge file downloaded as a CSV for speed and saved as a .xls and maybe there is some hidden formatting going on but I can’t see it. I have tried both methods in a clean workbook and they work fine but even copying the data out of the errant book into a new one does not solve it.

Reply | Quote

Hi John

Column B & C are populated by 2 letter country codes, for example NL, US, UK, DE, IN, SG etc. All I am putting in is either

=Concatenate(B11,” – “,C11) or as Prasad suggested =B11&” – “&C11 and both behave in the same way.

Having looked a bit more it may be that this is a huge file downloaded as a CSV for speed and saved as a .xls and maybe there is some hidden formatting going on but I can’t see it. I have tried both methods in a clean workbook and they work fine but even copying the data out of the errant book into a new one does not solve it.

Have you tried =CONCATENATE(TRIM(B11), ” – “, TRIM(C11)) ?

Reply | Quote

I could replicate the effect by putting the right thing in B11: SG followed by lots and lots of space characters.
What is actually in B11 ?

Reply | Quote

Thanks Prasad and Hans

However it still does the same as you can see from the screenshot below with dummy data, this is with both the & and the Concatenate formula?

Editted: helps if I attach the picture!!

I see no problem with the formula you posted.
Care to share a sample of your copy? It would be some other issue than the formula itself.

Reply | Quote

The most likely cause of the problem is that the cells in column B contain trailing spaces. The formulas suggested by Gfamily and mbarron should take care of that.

Reply | Quote

Were does the cursor appear if you click on the cell and then press your F2 key? If it does not appear immediately after the last letter, then you have spaces at the end of the field. Or highlight your column, go to Format / Column> / Autofit Selection.

Gfamily’s fomulas can be trimmed down to:
=TRIM(CONCATENATE(B11, ” – “, C11))

Reply | Quote

I just recreated it with no problem, including trailing spaces, which just appeared in the result when they were added to the source cells.

I did use the Function Arguments dialog box — typed =concatenate(
and then clicked on the fx Insert Function button to the left of the formula bar and completed the formula using the Function Arguments box. Perhaps that might work better.

Good luck!

Reply | Quote