How to combine sentences

[eran3185]

Hi all,

I have a large file with list of 6,000 rows (on column A).

I want to do this thing:
If the first character is not a number ,I want to continue the sentence above it etc...


For example:

I have this:

1. Bristol
2. Edinburgh
3. Cardiff
4. Liverpool
else
5. Leeds 6. Manchester 7. London
8, Belfast 9 Glasgow
10. Birmingham
else
11. Sheffield

I want to get this:

1. Bristol
2. Edinburgh
3. Cardiff
4. Liverpool else
5. Leeds
6. Manchester
7. London
8, Belfast
9 Glasgow
10. Birmingham else
11. Sheffield



TNX

 

[Fishboy]

Hi all,

I have a large file with list of 6,000 rows (on column A).

I want to do this thing:
If the first character is not a number ,I want to continue the sentence above it etc...


For example:

I have this:

1. Bristol
2. Edinburgh
3. Cardiff
4. Liverpool
else
5. Leeds 6. Manchester 7. London
8, Belfast 9 Glasgow
10. Birmingham
else
11. Sheffield

I want to get this:

1. Bristol
2. Edinburgh
3. Cardiff
4. Liverpool else
5. Leeds
6. Manchester
7. London
8, Belfast
9 Glasgow
10. Birmingham else
11. Sheffield



TNX

Hi eran3185,

I have spent some time battling with this via formula and, although I still suspect one of the gurus will be able to provide a working formula, I ended up looking at a VBA solution instead. I ended up needing to put a header at the top of columns A and B to keep life simple and prevent issues with LastRow.

To try this out, first make a COPY of your workbook, then add this code to a standard module and apply it to a button to run it.

Sub TEST()
' Defines variables
Dim Cell As Range, cRange As Range, tRange As Range
' Defines LastRow as last row of column B containing data
LastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row + 1
' Sets cRange as A2:A14 - You can increase this as required
Set cRange = Range("A2:A14")
' Sets cRange as B2:B14 - You can increase this as required
Set tRange = Range("B2:B14")
' For each cell in cRange
    For Each Cell In cRange
' If the cell is not blank and the next cell down does not say "else" then...
        If Cell.Value <> "" And Cell.Offset(1, 0).Value <> "else" Then
' The next available row in column B will be updated the cell value
            Range("B" & LastRow).Value = Cell.Value
' Else if the cell is not blank and the next cell down says "else" then...
        ElseIf Cell.Value <> "" And Cell.Offset(1, 0).Value = "else" Then
' The next available row in column B will be updated the cell value and " else"
            Range("B" & LastRow).Value = Cell.Value & " else"
' Clear the contents of the original "else" cell
                Cell.Offset(1, 0).ClearContents
        End If
' Increase where the last row is by 1
    LastRow = LastRow + 1
' Check next cell in cRange
    Next Cell
' For each cell in tRange
    For Each Cell In tRange
' If the cell value is blank then...
        If Cell.Value = "" Then
' Delete the whole row and shift upwards
            Cell.EntireRow.Delete Shift:=xlUp
        End If
' Check next cell in tRange
    Next Cell
End Sub

Feel free to amend the bold red A2:A14 and B2:B14 to suit how far down the sheet your data goes.

PS - I hope in your original data that lines 6, 7 and 9 should have been on their own rows.