OT. Can you get Excel to display the carriage-return as a character ?

I have made a mistake in not explaining the problem properly. Wordwrap etc. has nothing to do with the problem.
The problem is that there are hidden line-feed characters in the Excel file that won't display no matter what. An external translator won't understand that he must check each text if it is multi-line or not, and translate into a similar multi-line text in the other language.
If I have a text like this.
One
Two
Three
I get a translated text back like this:
Uno dos tres.

The solution is to search for [LF] and replace for [↵] before sending off to the translator.
And vice-versa when I get the files back from the translator. The [↵] will be visible, so the translator will not remove this character if I send some instruction together with the file.
I need to send off the texts to the translator today, as well as other stuff I need to do, so I dont have time to do the VBA code for this project but I will do it for the next project.

JesperMP said:

I have made a mistake in not explaining the problem properly. Wordwrap etc. has nothing to do with the problem.

Click to expand...

Seemed pretty clear from post #6 to me...

Hi Jesper
The hidden linefeed can be searched using "Ctrl J" as the search target so perhaps some search replace, and then search replace the opposite way to restore line feeds

cjd1965 said:

The hidden linefeed can be searched using "Ctrl J" as the search target so perhaps some search replace, and then search replace the opposite way to restore line feeds

Click to expand...

Quite funky this one. I did not know that you can do that.
It seems to work in .csv files, not .xlsx files.
Also, the search field looks empty, you dont get to see a $LF or something like that.

A quick test works some of the time. Not all of the time. I managed to get it find all multilines and replace the line-feeds with a visible characters. Now I cannot repeat this.
Not sure if it is me that does something wrong. Since the search field empty despite that there is a hidden character makes it harder to work with.

Man, Excel is frustrating.

So fed up with Excel.
Trying to search for $$$ and replace with the hidden CTRL J character I get this.
I dont want to experiment any more...

Thanks for all the suggestions so far, but it started as a simple question if there was a hidden setting in Excel to show unprintable characters, and as this has been answered plus I haven gotten some further valuable ideas, we can close this thread.

JesperMP said:

So fed up with Excel.
Trying to search for $$$ and replace with the hidden CTRL J character I get this.
I dont want to experiment any more...

Thanks for all the suggestions so far, but it started as a simple question if there was a hidden setting in Excel to show unprintable characters, and as this has been answered plus I haven gotten some further valuable ideas, we can close this thread.

Click to expand...

Haven't tested this, but it might be looking for $$$ by itself, so you may need to use wildcards on either side: *$$$*

EDIT: Just tested it on a spreadsheet I had open, it seems to work with the following options:

If you change the font to Terminal you will see a musical note as the CHAR(13)
But if you just type in the cell the CR is implied.
I also tried typing "abc" then Alt-013 (013 on the keypad for special characters) and it still does not put in a CR.
But if you go to a cell and put in =CHAR(97)&CHAR(13)
Then you get an "a" in regular fonts. But if you change to Terminal then you see the CR as a musical note.

I think this is the resolution

Hi Jesper, i looked into this a bit more

Look into the excel function 'Text to Columns' this will look into the cell containing the message and split each row into a column ready for translation

--Once returned, the message will be in say cells A1,B1,C1

in D1 type
Then set D1 format to wordwrap

D1 contains the new message

Works fine for me!



Possibly, he is using a counterfeit copy of excel.

Hi L33ER
My solution is for the entire scope, that is to take a cell with multi row text, split into columns for translation, then rebuild those columns back to multi row text

Eye patches and parrots are frowned upon here

I will look at this again when I get to the next project.

Quick way.


Use find/replace for char(10)




char(10) if you english excel and it same than ASCII LF (ALT ENTER or cntrl enter depending of softwares, 10th ascii code)


Then replace all with some characters which isn't normally used on text.



maybe ***


-> Send text for translater.


When you get translated text back.

-> replace *** with char(10) and import to TIA, linefeeds should be on same places than before tranlating.



Or make some formulas or script or macros to excel for same.