Excel string creation

  interzone55 16:14 01 Dec 09
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 09

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 09

Thanks VoG™ I'll give that a try

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

Sniper Elite 4 review: Headshotting Nazis has never felt so good

1995-2015: How technology has changed the world in 20 years

The Best Design, Illustration, Animation and VFX Awards of 2017

WWDC 2017 dates: How to get WWDC 2017 tickets, when is WWDC 2017 and more details announced