Concatenate cells with delimiter, new row for values after blank cells

**testerss** · 11-28-2011, 10:53 AM

Hi,

Have got a CSV file of religious verse, with each verse in a new row, verses separated by a blank cell:

Verse 1
abcdef
abcdefg
abcdefgh

Verse 2
abcdef
abdefg
abcdefgh

I need to import it into a database for use by an Android app, but the CSV has to be in the format of:

Tag Value
1 a,b,c
2 d,e,f

and so on.

Is it possible for a concatenate VBA function that will concatenate the rows, using a comma delimiter, but when a blank cell is encountered, to create concatenate the subsequent cells into a new row, so that it looks as per the Tag/Value combination above please?

I've looked at a few Concatenate VBA functions on here but haven't got figured out how to do it yet beyond manual range selection!

Thanks in advance!

**Steffen Thomsen** · 11-28-2011, 12:25 PM

It would be easier to help you if you uploaded a dummy workbook, containing data in the same structure.
With a sheet of the desired outcome.

Steffen

**JieJenn** · 11-28-2011, 12:28 PM

Use Text To Columns, Fixed Width option.

**testerss** · 11-28-2011, 01:34 PM

Ok sample csv file posted. The left is the text, the right is an example of how ideally it would be set out. Any assistance much appreciated! Thanks.

**Steffen Thomsen** · 11-28-2011, 02:49 PM

Hi,

Try this code

Sub steffen()
Dim verse As String
Dim sText

For Each cell In Range("A1:A" & Range("A60000").End(xlUp).Row)
    If InStr(cell.Text, "Verse") > 0 Then
        sText = Application.WorksheetFunction.Transpose(Range("A" & cell.Row).Resize(Range("A" & cell.Row).CurrentRegion.Rows.Count))
        For i = 1 To UBound(sText)
            On Error Resume Next
            verse = verse & sText(i) & ","
        Next i
    Else
        ' Do nothing
    End If
    Range("D60000").End(xlUp).Offset(1, 0) = verse
    verse = ""
Next cell
End Sub

**JieJenn** · 11-28-2011, 03:14 PM

Take a look at the file

**testerss** · 11-28-2011, 03:26 PM

Thanks Steffen, that worked perfectly!

JieJenn, thank you also for your efforts. Unfortunately it didn't contain a delimiter, which is crucial for Google App Engine to process the content properly.

Thanks very much indeed both! Now I'm going to go through the code and work out how you did it!