?
Solved

Syntax in excel 2007 for this

Posted on 2011-09-22
4
Medium Priority
?
225 Views
Last Modified: 2012-05-12
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
Comment
Question by:SMadhavi
[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
  • Learn & ask questions
4 Comments
 
LVL 12

Accepted Solution

by:
kgerb earned 2000 total points
ID: 36583233
Hello SMadhavi,
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

Open in new window

0
 
LVL 2

Expert Comment

by:sanofi-aventis
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

by:Saqib Husain, Syed
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

by:Patrick Matthews
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

Open in new window


That would allow a worksheet formula such as:

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

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.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question