Excel string creation

  interzone55 16:14 01 Dec 2009
Locked

I have a spreadsheet with customer account numbers in column A.

I need to merge these into a string separated by semi-colons, so I can manually import the string into a data-mining program

I could use B2=A2&";"&A3 etc, but there's around 100 entries in column A, and it keeps growing, so I was wondering if there's a way to do this with VBA or something...

  VoG II 16:31 01 Dec 2009

Press ALT + F11 to open the Visual Basic Editor, select Module from the Insert menu and paste into the white space on the right:



Sub concat()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("B2").Value = Join(Application.Transpose(Range("A2:A" & LR)), ";")
End Sub


Press ALT + Q to return to your sheet, Tools > Macro > Macros, click on concat then click the Run button.

  interzone55 16:37 01 Dec 2009

Thanks VoG™ I'll give that a try

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Alienware 17 R4 2017 review

Illustrator Sylvain Tegroeg created thousands of intricate line drawings for the mobile game…

Best iPad buying guide 2017

Comment télécharger une application indisponible en France ?