Splitting excel cells based on content

[chrishunt]

I have an excel workbook with a worksheet created by a dump from a
database (DOORS in this case). The first column is unique, the second
not. The second column may contain 0 to n identifiers that I want to
look up on a separate sheet. The lookup is easy when the cell in the
2nd column has either 0 or 1 identifiers in it but when there are 2 or
more (separated by a carriage return I think) my lookup fails as lookup
is using the enitre contents of the cell. What i'd like to be able to
do is to automatically (there's some 900+ rows in the worksheet) is to
automatically insert additional rows where the 2nd column has more than
one entry with with all other column values being replicated in the new
rows apart from the 2nd column where I'd like the multiple values to be
provide one per row. Then ideally removing the duplication in the
source cell. As an example the following would become

Col 1 Col2 Col3
1234 REF1 BLAH
REF 2

Col 1 Col2 Col3
1234 REF1 BLAH
1234 REF2 BLAH

Can anyone help?

 

[Bernie Deitrick]

Chris,

Try the macro below, which is written based on a CR (character 10) being used as the separator. You
can always change that if the separator isn't what you thought....

HTH,
Bernie
MS Excel MVP

Sub TryNow()
Dim myCell As Range
Dim myVals As Variant
Dim Delim As String

Delim = Chr(10)
For Each myCell In Range("B:B").SpecialCells(xlCellTypeConstants)
If InStr(1, myCell.Value, Delim) > 0 Then
myVals = Split(myCell.Value, Delim)
myCell.EntireRow.Copy
myCell.Resize(UBound(myVals)).EntireRow.Insert
myCell(0, 1).Resize(UBound(myVals) + 1).Value = _
Application.Transpose(myVals)
End If
Next myCell
Application.CutCopyMode = False
End Sub