You did not tells us if you wanted to fix the data in place or if you wanted
the concatenated text placed in another cell; I have assumed you wanted to
fix the data in place (that is, replace the existing data with the fixed
data). Give this macro a try...

Sub FixItems()
Dim X As Long, LastRow As Long, A As Range, Blanks As Range
Const StartRow As Long = 2
Const DataCol As String = "A"
LastRow = Cells(Rows.Count, DataCol).End(xlUp).Row
Set Blanks = Range(Cells(StartRow, DataCol), Cells(LastRow, _
DataCol)).SpecialCells(xlCellTypeConstants)
For Each A In Blanks.Areas
For X = A(1).Row + 1 To A(1).Row + A.Rows.Count - 1
Cells(X, DataCol).Value = Replace(Cells(X, DataCol).Value, " ", _
" " & Mid(A(1).Value, InStr(A(1). _
Value, " ") + 1) & " - ", , 1)
Next
Next
End Sub

Make sure you change the StartRow and DataCol constants (in the Const
statements) to reflect your actual data layout.

Rick Rothstein (MVP - Excel)




"Snowy" wrote in message
...

I have data in excel like below.
I want to concatenate 1 cell after every blank cell to there related
set of values.

For e.g:

0023.00 Brucellosis
0023.00 Brucella melitensis
0023.10 Brucella abortus
0023.20 Brucella suis
0023.30 Brucella canis
0023.80 Other brucellosis
0023.80 Infection by more than one organism
0023.90 Brucellosis, unspecified

Should Look like

0023.00 Brucellosis
0023.00 Brucellosis - Brucella melitensis
0023.10 Brucellosis - Brucella abortus
0023.20 Brucellosis - Brucella suis
0023.30 Brucellosis - Brucella canis
0023.80 Brucellosis - Other brucellosis
0023.80 Brucellosis - Infection by more than one organism
0023.90 Brucellosis - Brucellosis, unspecified

Eg 2.

0024.00 Glanders
0024.00 Infection by:
0024.00 Actinobacillus mallei
0024.00 Malleomyces mallei
0024.00 Pseudomonas mallei
0024.00 Farcy
0024.00 Malleus

It should look like

0024.00 Glanders
0024.00 Glanders - Infection by:
0024.00 Glanders - Actinobacillus mallei
0024.00 Glanders - Malleomyces mallei
0024.00 Glanders - Pseudomonas mallei
0024.00 Glanders - Farcy
0024.00 Glanders - Malleus


I have so many sets separating with the blank row.

Please help me on this.

Thanks
Snowy