Replacements in excel
Thread poster: Carlos Montilla

Carlos Montilla
Local time: 14:36
English to Spanish
+ ...
Nov 2, 2006

Hi all,
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!


Direct link Reply with quote
 

Antoní­n Otáhal
Local time: 14:36
Member (2005)
English to Czech
+ ...
VBA code Nov 2, 2006

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)

Next k
Next j


Unfortunately, I don't have time to test now if it actually works - please let me know.

HTH
Antonin

[Edited at 2006-11-02 11:36]


Direct link Reply with quote
 

Endre Both  Identity Verified
Germany
Local time: 14:36
Member (2002)
English to German
Use Alt+010 Nov 2, 2006

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).

Endre

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]


Direct link Reply with quote
 

Carlos Montilla
Local time: 14:36
English to Spanish
+ ...
TOPIC STARTER
Thank you!!! Nov 2, 2006

Hi Antonin,

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


Direct link Reply with quote
 

Antoní­n Otáhal
Local time: 14:36
Member (2005)
English to Czech
+ ...
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?

Antonin


Direct link Reply with quote
 

Hynek Palatin  Identity Verified
Czech Republic
Local time: 14:36
English to Czech
+ ...
ASAP Utilities Nov 2, 2006

ASAP Utilities should be able to do it:
http://www.asap-utilities.com/asap-utilities-excel-tools-tip.php?tip=219&utilities=Range


Direct link Reply with quote
 


To report site rules violations or get help, contact a site moderator:


You can also contact site staff by submitting a support request »

Replacements in excel

Advanced search






Déjà Vu X3
Try it, Love it

Find out why Déjà Vu is today the most flexible, customizable and user-friendly tool on the market. See the brand new features in action: *Completely redesigned user interface *Live Preview *Inline spell checking *Inline

More info »
TM-Town
Manage your TMs and Terms ... and boost your translation business

Are you ready for something fresh in the industry? TM-Town is a unique new site for you -- the freelance translator -- to store, manage and share translation memories (TMs) and glossaries...and potentially meet new clients on the basis of your prior work.

More info »



Forums
  • All of ProZ.com
  • Term search
  • Jobs
  • Forums
  • Multiple search