Solved

Syntax in excel 2007 for this

Posted on 2011-09-22
4
218 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
4 Comments
 
LVL 12

Accepted Solution

by:
kgerb earned 500 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 92

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

867 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now