Replace ; with hard return

S

Stephanie

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!
 
E

Evan

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.
 
G

Gord Dibben

Edit>Replace

What: ;

With: CRTL + j (or Alt + 0010 on the Numpad)

Replace all.

You will probably have to format to "Wrap Text".


Gord Dibben MS Excel MVP
 
D

Dave Peterson

If all the semicolons are followed by a space character, then I'd use:

what: ;_ (semicolon then spacebar)
with: ctrl-j
replace all
 
S

Stephanie

Thanks for the tip. I've used code in Access but am unfamiliar with using it
in Excel. I went with CTRL J. I appreciate your reply!
 
S

Stephanie

Thanks Dave, I did use the space bar.

Dave Peterson said:
If all the semicolons are followed by a space character, then I'd use:

what: ;_ (semicolon then spacebar)
with: ctrl-j
replace all
Click to expand...
 
S

Stephanie

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 said:
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 said:
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!
Click to expand...
Click to expand...
 
E

Evan

I jumped to the conclusion that Stephanie wanted one record per cell. Being
that the other solutions are so easy, can they be extended to getting one
record per cell?
 
G

Gord Dibben

Evan

Stephanie stated she wanted "hard carriage return" instead of semi-colon so
that's what I went with.

To get into separate cells would require a bit more manipulation.

Go ahead with the Edit>Replace to get the hard returns.

Select that cell and F2 then select all text in the cell and "Cut" or "Copy".

Select three cells in an adjacent column and "Paste"


Gord
 
Top