Solved

Syntax in excel 2007 for this

Posted on 2011-09-22
4
222 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 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

749 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