Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HARD RETURN IN A CELL | Excel Discussion (Misc queries) | |||
Replace double spaces with Hard return | Excel Worksheet Functions | |||
How do I split a one cell which has a hard return? | Excel Discussion (Misc queries) | |||
How do I insert a "hard return" in a cell? | Excel Discussion (Misc queries) | |||
hard return | New Users to Excel |