Replace ; with hard return
 

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!

Replace ; with hard return
 

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

"Stephanie" wrote in message
...
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!

Replace ; with hard return
 

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!

Replace ; with hard return
 

EditReplace

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

On Tue, 17 Apr 2007 11:14:04 -0700, 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!

Replace ; with hard return
 

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 wrote:

EditReplace

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

On Tue, 17 Apr 2007 11:14:04 -0700, 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!

--

Dave Peterson

Replace ; with hard return
 

Good point.



On Tue, 17 Apr 2007 14:16:58 -0500, Dave Peterson
wrote:

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 wrote:

EditReplace

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

On Tue, 17 Apr 2007 11:14:04 -0700, 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!

Replace ; with hard return
 

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

"Gord Dibben" wrote:

EditReplace

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

On Tue, 17 Apr 2007 11:14:04 -0700, 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!

Replace ; with hard return
 

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!

"Bob Umlas" wrote:

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

"Stephanie" wrote in message
...
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!

Replace ; with hard return
 

Thanks Dave, I did use the space bar.

"Dave Peterson" wrote:

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 wrote:

EditReplace

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

On Tue, 17 Apr 2007 11:14:04 -0700, 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!

--

Dave Peterson

Replace ; with hard return
 

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!

Replace ; with hard return
 

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" wrote:

EditReplace

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

On Tue, 17 Apr 2007 11:14:04 -0700, 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!

Replace ; with hard return
 

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 EditReplace 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


On Tue, 17 Apr 2007 13:40:04 -0700, Evan wrote:

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" wrote:

EditReplace

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

On Tue, 17 Apr 2007 11:14:04 -0700, 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!