I need help with parsing cells ...

[Henry Stock]

Data in one cell of a row may contain zero or more accounts names.

If the cell contains more than one account name, the names are sparated by a
comma

If there are more than one account names in the target cell of a row
I need to replicate that row n times where n is the number of account
names, and
place a copy of a unique account name in each row.

I need to figure out how I can:

Parse each target cell to determine how many account names I have

Copy and Insert additional rows such that there are n row where n is the
number of account names

Take the parsed string and put one account name per row in the target cell.

I think I can figure out how to insert the appropriate number of rows, but I
could use help on parsing the data in the cell in the first place.

-- Henry

--

 

[Chip]

roughly how many account names might there be? 5 or 500?

 

[Chip]

Here is what I got...

Sub parsinator()
cellval = Range("A1").Value

intcounter = 1
On Error GoTo Foundallcommas
Do Until x = 100
l = Len(cellval)
locationoffirstcomma = WorksheetFunction.Search(",", cellval, 1)
temp = Left(cellval, locationoffirstcomma - 1)
cellval = Right(cellval, l - locationoffirstcomma - 1)
Cells(intcounter, 2).Value = temp
intcounter = intcounter + 1
Loop

Foundallcommas:
Cells(intcounter, 2).Value = cellval


End Sub