Way to automate string creation from values in an Excel spreadsheet?

August 5, 2006 6:59 AM

Way to automate string creation from values in an Excel spreadsheet?

I have a large spreadsheet that I need break into parts and turn each part into a continuous string with each value separated by a comma- so essentially turn a list of

ABC1234
ABC4543
ABC5643

into: ABC1234,ABC4543,ABC5643

Any thoughts on how I can automate this process?

posted by mhaw to computers & internet (6 comments total)

How many cells are you looking to combine? Does the number of cells change drastically between your source files? If it's a relatively small amount, and it doesn't change often, you're probably best off doing it with the concatenation operator in a formula:

=A1&A2&A3&...&A20

If you need to deal with a much larger amount, you could do it in a macro, but be aware that the character limit for a cell is 32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar.
posted by odinsdream at 7:19 AM on August 5, 2006

Of course, modify the above to add commas:

=A1&","&A2&","&A3&","&...&","&A20
posted by odinsdream at 7:21 AM on August 5, 2006

in B1 = A1
in B2 = B1 & A2
Then autofill B2 down the column
posted by Lanark at 8:53 AM on August 5, 2006

Lanark's idea would work. It would also be simple to do with a macro:

Public Sub CombineCells
  Dim Combined As String
  Combined = ""

  For Each Cell in Selection
    Combined = Combined & Cell.Value & ","
  Next Cell

  Selection.Cells(1, 1).Value = Combined
End Sub

Then you'd just select the cells you want to combine and run the macro (which you can associate with a keyboard shortcut).

Unfortunately I don't have access to Excel right now and I'm not all too familiar with macro programming, so if I've made a mistake hopefully someone smarter than I am can fix it.
posted by zixyer at 12:24 PM on August 5, 2006

there's always the possibility that saving as a .csv file will work, which you can then open in a text editor.
posted by blind.wombat at 1:04 PM on August 5, 2006

I just tried my macro out in Excel -- it seems to work. To use it, go to Tools/Macro/Visual Basic Editor, then Insert/Module, and paste the code in. Then, to run it, Tools/Macros.

To trim off the comma that it inserts at the end of the string, you can put in
Combined = Left(Combined, Len(Combined) - 1)
before the second-to-last line.
posted by zixyer at 9:40 PM on August 5, 2006