This will do one cell (A5).
It first parses the text into an array (vArr) separated by commas.
It then takes the last vArr element and parses it again into another array (vLast) separated by semi-colons.
The constant portion of the text is determined using InStrRev.
The cell is increased to three rows (Resize)
The constant portion plus each element from vLast is added to the 3 cells in a loop.
(there has to be a better way <g)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


'--
Sub FromOneToMany()
Dim rng As Range
Dim rngCell As Range
Dim vArr As Variant
Dim vLast As Variant
Dim lngCount As Long
Dim lngLast As Long
Dim N As Long

Set rngCell = Range("A5")

vArr = VBA.Split(rngCell.Value, ",")
vLast = VBA.Split(vArr(UBound(vArr)), ";")
lngCount = UBound(vLast) + 1
Set rng = rngCell.Resize(lngCount, 1)
rng.Value = rngCell.Value
lngLast = InStrRev(rngCell.Value, ",") - 1
For N = lngCount To 1 Step -1
rng(N).Value = VBA.Left$(rngCell.Value, lngLast) & _
" " & VBA.Trim$(vLast(N - 1))
Next
Set rng = Nothing
Set rngCell = Nothing
End Sub
'--


"jfrick"

wrote in message
How can I handle a cell with multiple values in it? Can this be done
in Excel with macros?

I am new to macros and need help.
Here is my challenge:
I have a row of data that has one cell with multiple values in that
cell. I must be able to create multiple rows for each of these
values. For example:

Company name, company address, company city, company codes
ABC Widgets, 100 Main Street, New York, 005980542;017230022;017231132

Notice that the Company Code cell has semicolons separating three
company codes. The semicolons indicate that the particular company on
that row has more than one company code. I have many companies with
only one value. I also have companies that have up to 7 codes.

So my problem is how to take hundreds of these rows and create one row
per company code. The result of the above example should look like
this:

Company name, company address, company city, company codes
ABC Widgets, 100 Main Street, New York, 005980542
ABC Widgets, 100 Main Street, New York, 017230022
ABC Widgets, 100 Main Street, New York, 017231132

If I were doing this in a programming language, I might use logic like
this:

Go to the first row. Go to the company code cell.

Count the original number of semicolons. Copy the row I am on exactly
the number of semicolons. (Note: 6 codes will have 5 semicolons.
Since one row already exists, I only need to copy the row 5 times, and
thus I end up with 6 rows for this company.

On the row I am already on, the first row, delete all characters in
the company code cell from the first semicolon to the right. (This
does row 1 and code 1.)

Skip a row. Go to the company code cell. Locate semicolon 1. Delete
from the semicolon to the left. Locate the new first semicolon,
(formerly semicolon 2). Delete all characters from the first
semicolon to the right. (This does row 2 and code 2.)

Skip a row. Go to the company code cell. Locate semicolon 2.
(Remember that on this new row, the company code cell contains all of
the original company codes and semicolons.) Delete from the semicolon
to the left. Locate the new first semicolon, (formerly semicolon 3).
Delete all characters from the first semicolon to the right. (This
does row 3 and code 3.)

And so on until my row count equals the number of company codes. (An
alternate way of expressing this is to count the number of times I
skip to the next row, and this should match the number of semicolons I
found.)

What I don't know how to do is put this logic into a macro or do this
on a spreadsheet.

How do process and track the row? How do I keep count of the rows and
the semicolons when moving from row to the next row?

Any tips or thoughts would be greatly appreciated.