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 (4 answers total)

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

Best answer: 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

Best answer: 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