Hi, Also works a treat.
Thank you very much

"Chip Pearson" wrote:

The following code should work. You don't say where the combined
strings should be written to, so I write them one row at a time on
Sheet2. Change the value of the Dest variable to point to where the
combined strings should be written. Change the line

Set R = Range("A1") '<< or starting cell
to refer to the first string in the list.

The code assumes that the original data list resides on the worksheet
that is active in Excel.

Sub AAA()
Dim R As Range
Dim S As String
Dim LastRow As Long
Dim WS As Worksheet
Set WS = ActiveSheet
Dim Dest As Range

Set R = Range("A1") '<< or starting cell
With WS
LastRow = .Cells(.Rows.Count, R.Column).End(xlUp).Row
End With
Set Dest = Worksheets(2).Range("A1") '<<< CHANGE
Do Until R.Row LastRow
If StrComp(Left$(R.Text, 1), "", vbBinaryCompare) = 0 Then
If S < vbNullString Then
Dest.Value = S
Set Dest = Dest(2, 1)
S = vbNullString
End If
End If
S = S & " " & R.Text
Set R = R(2, 1)
Loop
Dest.Value = S
End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




On Thu, 1 Jan 2009 03:56:01 -0800, LaDdIe
wrote:

Happy New Year!

Could I have some help to find a solution to my task.

In column A
I have about 3000 rows of data,
The string in the first cell starts with the symbol , which then follow
with strings in preceeding cells which do not start with the symbol , then
again there may be a another string in a cell which does start with the
symbol .

I need a macro that looks from top to bottom in col A for a string that
starts with , then combine that string with other strings below that do not
start with , if the string below starts with then it should stop combining.

Simply put the symbol denotes the start of a sentence.

Any help will save my little fingers goinig numb