Thanks for taking the time to write this equation. Wow! I'll have to practice
with that. I ended up using "replace" ; <space with CTRL J. I appreciate
your help!

"Evan" wrote:

A hard way w/o VBA, use
Data Text to Columns [x] Delimiter Next Other = ;
to columize the data. Then in another column, assuming your data starts at
the top-left and has as most 4 columns, use integer and modulo arithmetic:
=OFFSET($A$1,INT((ROW()-1)/5),MOD(ROW()-1,5))
and autofill down. There will be some clean-up required, e.g., put €œzzz€� in
the 5th column and Autofilter out the zero cells and change €œzzz€� to blank.


"Stephanie" wrote:

Hi. We are in the middle of a conversion and I'd like to make it easier.

We have a column that contains:

Chair; Desk; File
Chair; File
Desk; File

Items that are separated by a semi-colon.
Instead, I need these items separated by a hard carriage return within the
column:

Chair
Desk
File

Chair
File

File
Desk

Any suggestions are appreciated!