Replacements in excel
Thread poster: Carlos Montilla

Carlos Montilla
Local time: 20:53
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: 20:53
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: 20:53
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: 20:53
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: 20:53
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: 20:53
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






SDL MultiTerm 2017
Guarantee a unified, consistent and high-quality translation with terminology software by the industry leaders.

SDL MultiTerm 2017 allows translators to create one central location to store and manage multilingual terminology, and with SDL MultiTerm Extract 2017 you can automatically create term lists from your existing documentation to save time.

More info »
PDF Translation - the Easy Way
TransPDF converts your PDFs to XLIFF ready for professional translation.

TransPDF converts your PDFs to XLIFF ready for professional translation. It also puts your translations back into the PDF to make new PDFs. Quicker and more accurate than hand-editing PDF. Includes free use of Infix PDF Editor with your translated PDFs.

More info »



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