Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I search and replace with a line break?
Want to use Ctrl-H to replace a specific character in a range of cells with a
line break (Alt+Enter) - how do I access special characters? (^p didn't work) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I search and replace with a line break?
In the "Find What" box of the Replace dialog, hold down your left ALT key
and type 0010 on the numeric keypad (not the number keys above the letters). -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Impish" wrote in message ... Want to use Ctrl-H to replace a specific character in a range of cells with a line break (Alt+Enter) - how do I access special characters? (^p didn't work) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I search and replace with a line break?
May well work but using a laptop
"Chip Pearson" wrote: In the "Find What" box of the Replace dialog, hold down your left ALT key and type 0010 on the numeric keypad (not the number keys above the letters). -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Impish" wrote in message ... Want to use Ctrl-H to replace a specific character in a range of cells with a line break (Alt+Enter) - how do I access special characters? (^p didn't work) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I search and replace with a line break?
use ctrl-j instead.
But most laptops have some sort of Fn key that will make the keys act as number pad keys. (but ctrl-j is easier <bg.) Impish wrote: May well work but using a laptop "Chip Pearson" wrote: In the "Find What" box of the Replace dialog, hold down your left ALT key and type 0010 on the numeric keypad (not the number keys above the letters). -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Impish" wrote in message ... Want to use Ctrl-H to replace a specific character in a range of cells with a line break (Alt+Enter) - how do I access special characters? (^p didn't work) -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I search and replace with a line break?
I'm trying the reverse of this issue. I have cells that has some kind of
special character in it. I'm assuming its a return but I don't know for certain. I don't have access to the original data. I want to replace whatever it is with a space. I tried the looking for alt 0010, alt 0013, alt 0015 ( 3 seperate finds) but nothing was found. is there a way I can find out exactly what special characters this thing is? Excel 2000 Thanks John "Chip Pearson" wrote: In the "Find What" box of the Replace dialog, hold down your left ALT key and type 0010 on the numeric keypad (not the number keys above the letters). -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Impish" wrote in message ... Want to use Ctrl-H to replace a specific character in a range of cells with a line break (Alt+Enter) - how do I access special characters? (^p didn't work) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I search and replace with a line break?
It might be the non-breaking space character, 160.
If it is in, say, the fifth character position in cell A1, then try this to find out its code: =CODE(MID(A1,5,1)) Adjust the cell reference and the 5 to suit. Hope this helps. Pete On Feb 26, 8:15 pm, JohnH wrote: I'm trying the reverse of this issue. I have cells that has some kind of special character in it. I'm assuming its a return but I don't know for certain. I don't have access to the original data. I want to replace whatever it is with a space. I tried the looking for alt 0010, alt 0013, alt 0015 ( 3 seperate finds) but nothing was found. is there a way I can find out exactly what special characters this thing is? Excel 2000 Thanks John "Chip Pearson" wrote: In the "Find What" box of the Replace dialog, hold down your left ALT key and type 0010 on the numeric keypad (not the number keys above the letters). -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Impish" wrote in message ... Want to use Ctrl-H to replace a specific character in a range of cells with a line break (Alt+Enter) - how do I access special characters? (^p didn't work)- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I search and replace with a line break?
Jihn
To really see what's in there, download Chip Pearson's CELLVIEW add-in. http://www.cpearson.com/excel/CellView.htm Gord Dibben MS Excel MVP On Mon, 26 Feb 2007 12:15:05 -0800, JohnH wrote: I'm trying the reverse of this issue. I have cells that has some kind of special character in it. I'm assuming its a return but I don't know for certain. I don't have access to the original data. I want to replace whatever it is with a space. I tried the looking for alt 0010, alt 0013, alt 0015 ( 3 seperate finds) but nothing was found. is there a way I can find out exactly what special characters this thing is? Excel 2000 Thanks John "Chip Pearson" wrote: In the "Find What" box of the Replace dialog, hold down your left ALT key and type 0010 on the numeric keypad (not the number keys above the letters). -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Impish" wrote in message ... Want to use Ctrl-H to replace a specific character in a range of cells with a line break (Alt+Enter) - how do I access special characters? (^p didn't work) |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I search and replace with a line break?
Thanks Pete, I'm halfway there. using your formula is see 13 in the position
of the special character. However when I do a find Alt 0013 using the left alt and the number pad all it finds is cells that are blank. I know its only the one space I''m looking at because when I use the formula for the space before or after the special space I get the ascii value of a valid letter. "Pete_UK" wrote: It might be the non-breaking space character, 160. If it is in, say, the fifth character position in cell A1, then try this to find out its code: =CODE(MID(A1,5,1)) Adjust the cell reference and the 5 to suit. Hope this helps. Pete On Feb 26, 8:15 pm, JohnH wrote: I'm trying the reverse of this issue. I have cells that has some kind of special character in it. I'm assuming its a return but I don't know for certain. I don't have access to the original data. I want to replace whatever it is with a space. I tried the looking for alt 0010, alt 0013, alt 0015 ( 3 seperate finds) but nothing was found. is there a way I can find out exactly what special characters this thing is? Excel 2000 Thanks John "Chip Pearson" wrote: In the "Find What" box of the Replace dialog, hold down your left ALT key and type 0010 on the numeric keypad (not the number keys above the letters). -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Impish" wrote in message ... Want to use Ctrl-H to replace a specific character in a range of cells with a line break (Alt+Enter) - how do I access special characters? (^p didn't work)- Hide quoted text - - Show quoted text - |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I search and replace with a line break?
Chip, Is it possible to change the line break to "Text to Columns"
"Chip Pearson" wrote: In the "Find What" box of the Replace dialog, hold down your left ALT key and type 0010 on the numeric keypad (not the number keys above the letters). -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Impish" wrote in message ... Want to use Ctrl-H to replace a specific character in a range of cells with a line break (Alt+Enter) - how do I access special characters? (^p didn't work) |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I search and replace with a line break?
I'm not quite sure what your question means, but you can also use ctrl-j instead
of alt-0010 in both the edit|replace dialog as well as the Other character in the data|text to columns dialog. Kathy wrote: Chip, Is it possible to change the line break to "Text to Columns" "Chip Pearson" wrote: In the "Find What" box of the Replace dialog, hold down your left ALT key and type 0010 on the numeric keypad (not the number keys above the letters). -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Impish" wrote in message ... Want to use Ctrl-H to replace a specific character in a range of cells with a line break (Alt+Enter) - how do I access special characters? (^p didn't work) -- Dave Peterson |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I search and replace with a line break?
Thanks Dave, I found the answer! :)
Assuming your data starts at A1, put the following formula in B1: =SUBSTITUTE(A1," ","~") In between the empty quotes, instead of a space hit Alt-Enter. Copy the formula down to match your list of data. Then select all of the formulas and Copy, then Paste / Special / Values. Now do a Text To Columns using "~" as the delimiter. "Kathy" wrote: Chip, Is it possible to change the line break to "Text to Columns" "Chip Pearson" wrote: In the "Find What" box of the Replace dialog, hold down your left ALT key and type 0010 on the numeric keypad (not the number keys above the letters). -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Impish" wrote in message ... Want to use Ctrl-H to replace a specific character in a range of cells with a line break (Alt+Enter) - how do I access special characters? (^p didn't work) |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I search and replace with a line break?
This may be easier:
=SUBSTITUTE(A1,char(10),"~") But you should be able to use alt-0010 or ctrl-j in that text to columns dialog. Kathy wrote: Thanks Dave, I found the answer! :) Assuming your data starts at A1, put the following formula in B1: =SUBSTITUTE(A1," ","~") In between the empty quotes, instead of a space hit Alt-Enter. Copy the formula down to match your list of data. Then select all of the formulas and Copy, then Paste / Special / Values. Now do a Text To Columns using "~" as the delimiter. "Kathy" wrote: Chip, Is it possible to change the line break to "Text to Columns" "Chip Pearson" wrote: In the "Find What" box of the Replace dialog, hold down your left ALT key and type 0010 on the numeric keypad (not the number keys above the letters). -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Impish" wrote in message ... Want to use Ctrl-H to replace a specific character in a range of cells with a line break (Alt+Enter) - how do I access special characters? (^p didn't work) -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ALT+ENTER = line break | Excel Discussion (Misc queries) | |||
Search and Replace end of Line and Insert Alt-Enter | Excel Discussion (Misc queries) | |||
Break cell into multiple lines by line break | Excel Discussion (Misc queries) | |||
Line Break | Excel Discussion (Misc queries) | |||
line break in a cell | Excel Worksheet Functions |