I have a spreadsheet that looks something like this:

   A B C D E ...
1| 1 0 0 1 0
2| 0 1 0 0 1
3| 0 0 0 1 0
4| 1 0 0 0 0

I'd like to concatenate row 1 in column F so that it returns: 10010

But entering =concatenate(A1:E1) returns an error #VALUE! and using an array formula ({=concatenate(A1:E1)}) returns just 1.

This method gets me close, but it removes the formula so I can't drag down and concatenate all rows.

Why does Excel make concatenating so hard? Is there another way to do this without using VBA?

up vote 4 down vote accepted

In F1 enter:

=A1 & B1 & C1 & D1 & E1

and copy down.

enter image description here

EDIT#1:

If you want to bite the bullet and use VBA, then consider this tiny UDF

Public Function KonKatenate(rIN As Range) As String
    Dim r As Range
    For Each r In rIN
        KonKatenate = KonKatenate & r.Text
    Next r
End Function

User Defined Functions (UDFs) are very easy to install and use:

  1. ALT-F11 brings up the VBE window
  2. ALT-I ALT-M opens a fresh module
  3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

  1. bring up the VBE window as above
  2. clear the code out
  3. close the VBE window

To use the UDF from Excel:

=KonKatenate(A1:Z1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and for specifics on UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Macros must be enabled for this to work!

The CONCATENATE function in Excel is frustratingly limited. As far as I can see, your only option for having a fill-able concatenation formula is to bite the bullet and enter the address of each term as a separate argument.

=CONCATENATE(A1,B1,C1,D1,E1)

Sorry, that's the best I can offer without VBA. I hope you don't have a ton of columns.

  • I do have lots of columns. Of course I do! I'll mark this correct unless there's a deus ex machina of an answer in the next couple days. – samthebrand Feb 25 '15 at 20:57

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.