Concatenate and Transpose with ,=

[shauste]

Hello,

I would like to concatenate and transpose cells in a column and separate values with an equal (=) and comma (,) so that the results would look like: =1234,=5678,=9012,=3456

This is the formula I have to far for the comma but I can't get the equal to work. =CONCATENATE(TRANSPOSE(A2:A20&","))

Any help is greatly appreciated.

SHAUSTE

[alansidman]

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[AliGW]

The equals sign is a reserved character used for opening formulae.

[FlameRetired]

shauste if your profile hasn't changed since 03-22-2016 to Office 365 you are going to need VBA.

Here is the code for a user defined function that does that. It was written by tigeravatar.

Please Login or Register  to view this content.

1. Copy code above.
2. Press Alt and F11 on your keyboard to open VB Editor
3. Click on Insert and select Module
4. Paste code into Module and close VB Editor.

Don't forget to save your worbook as Macro-Enabled workbook. (It is already done in the attached.)

Then this formula

Please Login or Register  to view this content.

No need to transpose since the comma separators and single cell output make column/row issues superfluous. Note the leading "=" appended to the formula.