Concatenate formula

[Manolocs]

Hello, I want to concatenate all words with a space in the same row but some rows has more than 30 columns

How can I make simple this formula maybe with function?

=CONCATENATE(A1," ",B1," ",C1," "......... until there is text in the cell?

Thanks in advance

 

[BarryL]

in cell A2 put the below formula

=A1

in cell B2 put the below

=A2&" "&B1

then drag across.

 

[BarryL]

this is just to show you.... this can be performed on a separate worksheet if needed

 

[Manolocs]

Hi Barry, when I drag across comes only the A1 in all columns. It is strange, this works in Windows but not in Mac ????

 

[BarryL]

there should be no issues in this formula.

for example... in A1, you have the value "Hi"

so in A2 , you have the formula "=A1". Therefore, A2 also equals "Hi".

in B1, you have the value "Bob". In B2, you have the formula "=A2" "&B1". So the value in B2 is now "Hi Bob"

In C1 is "how". In C2 is "=B2&" "&C1".

B2 = Hi Bob
C1 = how

so now C2 = Hi Bob how

and so on.

 

[Rick Rothstein]

Hello, I want to concatenate all words with a space in the same row but some rows has more than 30 columns

How can I make simple this formula maybe with function?

=CONCATENATE(A1," ",B1," ",C1," "......... until there is text in the cell?

You can do this using a UDF (user defined function)...

[table="width: 500"]
[tr]
	[td]Function WordsAcrossRow(RowNumber As Long) As String
  WordsAcrossRow = Application.Trim(Join(Application.Index(Rows(RowNumber).Value, 1, 0), " "))
End Function[/td]
[/tr]
[/table]

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use WordsAcrossRow just like it was a built-in Excel function. For example, concatenating the words on Row 1...

=WordsAcrossRow(1)

If you want to be able to copy the formula down, then you can specify the row number this way...

=WordsAcrossRow(ROWS($1:1))

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Manolocs]

Anyway is working maybe is a problem with Excel for Mac. Thank you very much