Replacements in excel
Thread poster: Carlos Montilla
I have to copy a large column from word to excel, and each cell includes line breaks.
So, when pasting, excel counts every line break as a change of cell and the copy-pasting process just doesn't work.
So I came up with a "brilliant" idea. I replaced every line break in word for the formula XCXCXCXCX and copied the whole thing in excel. There was no problem. All the cells were copied.
Now I need to replace the formula XCXCXCXCX back into a line break in excel, but when I ask excel to replace it with ^l or ^p, it literally replaces it by ^l or ^p, and not with a line break.
SO, I need to know how to replace something with a line break in Excel, or how to paste from word without excel transforming ecvery line break into a new cell. I'm sure this is possible.
Please, help me!
I believe the following code will do what you need in the "top left-hand corner" n×n range of cells:
For j = 1 to n
For k = 1 to n
Cells (j,k) = Replace(Cells(j,k), "XCXCXCXCX", vbNewLine)
Unfortunately, I don't have time to test now if it actually works - please let me know.
[Edited at 2006-11-02 11:36]
| | Endre Both
Local time: 23:11
English to German
Instead of entering ^l or similar as a replacement text, try entering the decimal code of the line break, which is 10.
To do so, hold down the Alt button (the left one if you have two of them) and press 010. The replacement text box won't show anything (not even a space), but if you run the replacement, it should work (it does so here).
One word of caution: If your text has mixed formatting within a single cell (different colours, bold/non-bold etc.), a replacement operation seems to make this formatting disappear (at least in Excel 2000). I'm not sure (and I would like to know) whether this is true of later versions of Excel.
[Bearbeitet am 2006-11-02 11:45]
I did not understand your system. I'm not a proficient excel user.
However, I've used Endre's system and it works! It's just fantastic.
You can't imagine the amount of time you've saved me.
THANK YOU VERY MUCH TO BOTH FOR YOUR HELP!!!!
| That's neat, Endre || Nov 2, 2006 |
I did not know that one. Is there a similar way of entering Unicode chars into the replacement dialog?
| | Hynek Palatin
Local time: 23:11
English to Czech
To report site rules violations or get help, contact a site moderator:
You can also contact site staff by submitting a support request »