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

JesperMP

Lifetime Supporting Member + Moderator
Join Date
Feb 2003
Location
ᚴᚬᛒᛅᚾᚼᚬᚠᚾ
Posts
16,239
This is for editing HMI texts en masse in Excel.

My problem is that in my HMI texts, some are in multiple lines, even if they are single texts. The HMI texts can be exported from TIA to Excel and imported to TIA from Excel.

My problem is that when you view the individual rows in Excel, the carriage-return is 'hidden' so to speak. Only if you select a text, will it be shown as multilines.

When browsing the texts they are displayed like this:
Line1Line2Line3

when a text is selected it displays like this:
Line1
Line2
Line3

It is particularly a problem when we send the texts to be translated by an external translator. They almost always miss that the texts are multi-line, so we have to modify the texts after we get them back from the translator, and that can be a big job.

In Word there is an option to 'display non-printable characters' but I cannot find something similar in Excel ..
Any ideas ?
 
I'm not aware of a way to display carriage returns or line feeds in Excel either, but conditional highlighting might be helpful.

This link suggests a method you can use to highlight cells that contain the linefeed character (CHAR(10)).

It won't resolve your problem but at least provides a better visual indication of multiline content.

Here's the conditional highlighting formula.
=ISNUMBER(FIND(CHAR(10),A1))

excel-multiline-highlight.PNG
 
My problem is that when you view the individual rows in Excel, the carriage-return is 'hidden' so to speak. Only if you select a text, will it be shown as multilines.

When browsing the texts they are displayed like this:
Line1Line2Line3

when a text is selected it displays like this:
Line1
Line2
Line3


Maybe I am missing something here. Where exactly (in the cell, in the entry/address bar, wherever?) is it shown as
Line1Line2Line3
vs
Line1
Line2
Line3
Could you provide screenshots?

Do you have word-wrap turned on?
 
No it is not word wrap.

Here are some screenshots from the texts of a button that are configured as multi-line:
When browsing, you cannot see the line-breaks:
When a cell is selected, it shows how the text is really formatted:

excel_multiline_browsing.png excel_multiline_cell_selected.png
 
A little digging suggests there is not any way to make excel actually display such characters.

Perhaps you could do something like insert a glyph (eg ↵) just before any carriage returns to make them obvious.

Line Breaks.png
 
If I save as CSV, open in Notepad++ and select "show all characters", there is an [LF] after each of the lines in the multi-line texts.

In TIA, when you edit the text, the line breaks are displayed with a special symbol. See screenshot.
(I cannot paste the symbol it into the post, because it gets interpreted as a new-line, not as a character that must be displayed as itself).

So what I am after is that Excel also displays a symbol.
My hope was that there was a setting somewhere similar to the 'display non-printable characters' as there is in Word.
If it gets complicated, just forget it.

TIA_multiline_editing.png
 
Perhaps you could do something like insert a glyph (eg ↵) just before any carriage returns to make them obvious.
Consider that there are thousands of texts that would have to be manipulated in this way.
I guess I could save as CSV, then have a script that seaches for the [LF] character, and replaces it with the [↵], and when the file returns from the translator, I could reverse the process.

I have so much to do that I dont have the time to implement time-saving measures ..:rolleyes:
 
Consider that there are thousands of texts that would have to be manipulated in this way.
I guess I could save as CSV, then have a script that seaches for the [LF] character, and replaces it with the [↵], and when the file returns from the translator, I could reverse the process.
Exactly why I suggested a simple substitution/insertion that could be automated.

I have so much to do that I dont have the time to implement time-saving measures ..:rolleyes:
I can certainly sympathise...



PS The symbol in your screenshot is called a pilcrow, in use since the Middle Ages to mark a new train of thought and predating the modern concept of fully discrete paragraphs entirely.
 
I certainly will consider the idea.
In my experience, even if the task is simple, doing anything with VBA in Excel easily costs at least a day when you have to test and document it.
I hoped there was a simple setting in Excel.

PS The symbol in your screenshot is called a pilcrow, in use since the Middle Ages to mark a new train of thought and predating the modern concept of fully discrete paragraphs entirely.
This information will be handy when I get a spot in "do you want to be a millionaire".
 
' In Word there is an option to 'display non-printable characters' but I cannot find something similar in Excel ..
Any ideas ? '

Hi, I don't know about Word however when I was working with logic (Studio 5000 V.30) we exported descriptors to Excel CSV. The multi-line descriptor text would be displayed in Excel as "All Robots$NTo home$NPosition. (Example)
The $N was the carriage return. So when it was imported back into logic, the multiline displayed without the carriage returns in the text.
And of course if we had to edit or add texts to the descriptors in Excel, one had to use those characters to create multiline texts.
I did quite a bit with Siemens HMI (TIA V. 15.1 update 5) for GM. They use an Excel tool to create logic and HMI programs however I certainly remember those "pilcrows" . Hope this helps
 

Similar Topics

Hi, I'm trying to export data from a DataGrid to Excel using VBA, but I'm getting an error "Object doesn't support this property or method". The...
Replies
0
Views
57
I don't know if this is the right place for the subject at least I'll try. The company has one of the oldest computers that is a master Windows...
Replies
5
Views
719
Hi, I'm just looking for a simple way to make a button in excel (via VBA I presume) to toggle a bit in RSLogix 5000. I just got FactoyTalkLinx...
Replies
9
Views
488
Hello, I want to send the data from Rslogix500 to Excel. I created a connection between RSlinx and Excel. The values in Excel automatically...
Replies
5
Views
1,130
Hi guys, Im back again, this time I cannot minimized excel even though i put 6 on the exec mode.. this is my cicode.. Exec("C:\Program...
Replies
1
Views
1,079
Back
Top Bottom