Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

Syntax in excel 2007 for this

Posted on 2011-09-22
Medium Priority
227 Views
I have a column with names in it
a
b
c
d
e
f
g
say 200 rows

I need to form a string such that the last row colum b
has a;b;c;d;e;f;g;h; ......
I need the code to do this
Thanks

YRKS
0
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 12

Accepted Solution

kgerb earned 2000 total points
ID: 36583233
This should do the trick.  Change the numbers 3 and 7 to the row numbers where your list of names starts and ends.

Kyle
``````Sub Concat()
Dim i As Long, c As String
For i = 3 To 7
c = c & "; " & Cells(i, 1)
Next i
c = Right(c, Len(c) - 2)
Cells(i - 1, 2) = c
End Sub
``````
0

LVL 2

Expert Comment

ID: 36583265
If this is adhoc and does not need to be repeated. I have to do this many times say to create a SQL in statement built from an extract. You Can use a formula like this an a blank column on the sheet.
In the second row of data, =( A1 & ";" & A2 ) and copy this formula down to the last row. The string in the last row will be what you want.
0

LVL 43

Expert Comment

ID: 36583294
Try this code

Sub listcat()
Dim rw As Long, lst As String, cel As Range
rw = Range("A" & Rows.Count).End(xlUp).Row
lst = [a1]
For Each cel In Range("A2:A" & rw)
lst = lst & ";" & cel
Next cel
Cells(rw, 2) = lst
End Sub
0

LVL 93

Expert Comment

ID: 36583332
Or as a function:

``````Function ConcRange(Substrings As Range, Optional Delim As String = "", _
Optional AsDisplayed As Boolean = False, Optional SkipBlanks As Boolean = False)

' Function by Patrick Matthews, Matt Vidas, and rberke

' Concatenates a range of cells, using an optional delimiter.  The concatenated
' strings may be either actual values (AsDisplayed=False) or displayed values.
' If NoBlanks=True, blanks cells or cells that evaluate to a zero-length string
' are skipped in the concatenation

' Substrings: the range of cells whose values/text you want to concatenate.  May be
' from a row, a column, or a "rectangular" range (1+ rows, 1+ columns)

' Delimiter: the optional separator you want inserted between each item to be
' concatenated.  By default, the function will use a zero-length string as the
' delimiter (which is what Excel's CONCATENATE function does), but you can specify
' your own character(s).  (The Delimiter can be more than one character)

' AsDisplayed: for numeric values (includes currency but not dates), this controls
' whether the real value of the cell is used for concatenation, or the formatted
' displayed value.  Note for how dates are handled: if AsDisplayed is FALSE or omitted,
' dates will show up using whatever format you have selected in your regional settings
' for displaying dates.  If AsDisplayed=TRUE, dates will use the formatted displayed
' value

' SkipBlanks: Indicates whether the function should ignore blank cells (or cells with
' nothing but spaces) in the Substrings range when it performs the concatenation.
' If NoBlanks=FALSE or is omitted, the function includes blank cells in the
' concatenation.  In the examples above, where NoBlanks=False, you will see "extra"
' delimiters in cases where the Substrings range has blank cells (or cells with only
' spaces)

Dim CLL As Range

For Each CLL In Substrings.Cells
If Not (SkipBlanks And Trim(CLL) = "") Then
ConcRange = ConcRange & Delim & IIf(AsDisplayed, Trim(CLL.Text), Trim(CLL.Value))
End If
Next CLL

ConcRange = Mid\$(ConcRange, Len(Delim) + 1)

End Function
``````

That would allow a worksheet formula such as:

=ConcRange(A1:A200,";")
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Suggested Courses
Course of the Month8 days, 16 hours left to enroll