Replace ; with hard return

[Guest]

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!

 

[Bob Umlas]

Try this:
Sub Answer()
cells.Replace ";",vblf
cells.WrapText=true
End Sub

 

[Guest]

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.

 

[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

 

[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

 

[Gord Dibben]

Good point.

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

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

 

[Guest]

CRTL J is Sweet!
Thanks for the details- I appreciate your help.

 

[Guest]

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!

 

[Guest]

Thanks Dave, I did use the space bar.

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

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

 

[Guest]

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!

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.

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!

 

[Guest]

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?

 

[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