You are not registered yet. Please click here to register!


 
 
plc storereviewsdownloads
This board is for PLC Related Q&A ONLY. Please DON'T use it for advertising, etc.
 
Try our online PLC Simulator- FREE.  Click here now to try it.

New Here? Please read this important info!!!


Go Back   PLCS.net - Interactive Q & A > PLCS.net - Interactive Q & A > LIVE PLC Questions And Answers

Reply
 
Thread Tools Display Modes
Old January 10th, 2022, 07:00 AM   #1
JesperMP
Lifetime Supporting Member + Moderator
Denmark

JesperMP is offline
 
JesperMP's Avatar
 
Join Date: Feb 2003
Location: Copenhagen.
Posts: 15,092
OT. Can you get Excel to display the carriage-return as a character ?

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 ?
__________________
Jesper
See my profile interests for Q&A
  Reply With Quote
Old January 10th, 2022, 09:16 AM   #2
tvey
Lifetime Supporting Member
Canada

tvey is offline
 
Join Date: Mar 2005
Location: Halifax, NS
Posts: 83
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))
Attached Images
File Type: png excel-multiline-highlight.PNG (4.2 KB, 164 views)
  Reply With Quote
Old January 10th, 2022, 09:58 AM   #3
chud
Member
South Africa

chud is offline
 
chud's Avatar
 
Join Date: Oct 2007
Location: KALAHARI
Posts: 1,045
Have you opened your file in notepad++ to see what it looks like in there?
  Reply With Quote
Old January 10th, 2022, 10:11 AM   #4
JesperMP
Lifetime Supporting Member + Moderator
Denmark

JesperMP is offline
 
JesperMP's Avatar
 
Join Date: Feb 2003
Location: Copenhagen.
Posts: 15,092
Quote:
Originally Posted by chud View Post
Have you opened your file in notepad++ to see what it looks like in there?
I am not experienced in Notepad++. Can it open Excel files natively ?
I tried to get Notepad++ and open one of the exported files. It displayed as gibberish.
__________________
Jesper
See my profile interests for Q&A
  Reply With Quote
Old January 10th, 2022, 10:32 AM   #5
drbitboy
Lifetime Supporting Member
United States

drbitboy is online now
 
drbitboy's Avatar
 
Join Date: Dec 2019
Location: Rochester, NY
Posts: 4,174
Quote:
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?
__________________
i) Take care of the bits, and the bytes will take care of themselves.
ii) There is no software problem that cannot be solved with another layer of indirection.
iii) I solemnly swear that I am up to no good
  Reply With Quote
Old January 10th, 2022, 10:39 AM   #6
JesperMP
Lifetime Supporting Member + Moderator
Denmark

JesperMP is offline
 
JesperMP's Avatar
 
Join Date: Feb 2003
Location: Copenhagen.
Posts: 15,092
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:
Attached Images
File Type: png excel_multiline_browsing.png (1.4 KB, 40 views)
File Type: png excel_multiline_cell_selected.png (2.7 KB, 39 views)
__________________
Jesper
See my profile interests for Q&A
  Reply With Quote
Old January 10th, 2022, 10:41 AM   #7
AlfredoQuintero
Lifetime Supporting Member
Japan

AlfredoQuintero is offline
 
Join Date: Feb 2015
Location: Yokohama
Posts: 815
Can you save the Excel sheet in CSV format so you can open it in Notepad++?
  Reply With Quote
Old January 10th, 2022, 10:43 AM   #8
plvlce
Lifetime Supporting Member
United States

plvlce is offline
 
Join Date: May 2017
Location: Michigan
Posts: 395
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.
Attached Images
File Type: png Line Breaks.png (3.0 KB, 148 views)
  Reply With Quote
Old January 10th, 2022, 10:55 AM   #9
JesperMP
Lifetime Supporting Member + Moderator
Denmark

JesperMP is offline
 
JesperMP's Avatar
 
Join Date: Feb 2003
Location: Copenhagen.
Posts: 15,092
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.
Attached Images
File Type: png TIA_multiline_editing.png (1.2 KB, 148 views)
__________________
Jesper
See my profile interests for Q&A
  Reply With Quote
Old January 10th, 2022, 11:13 AM   #10
JesperMP
Lifetime Supporting Member + Moderator
Denmark

JesperMP is offline
 
JesperMP's Avatar
 
Join Date: Feb 2003
Location: Copenhagen.
Posts: 15,092
Quote:
Originally Posted by plvlce View Post
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 ..
__________________
Jesper
See my profile interests for Q&A
  Reply With Quote
Old January 10th, 2022, 11:33 AM   #11
plvlce
Lifetime Supporting Member
United States

plvlce is offline
 
Join Date: May 2017
Location: Michigan
Posts: 395
Quote:
Originally Posted by JesperMP View Post
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.

Quote:
I have so much to do that I dont have the time to implement time-saving measures ..
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.
  Reply With Quote
Old January 10th, 2022, 11:40 AM   #12
JesperMP
Lifetime Supporting Member + Moderator
Denmark

JesperMP is offline
 
JesperMP's Avatar
 
Join Date: Feb 2003
Location: Copenhagen.
Posts: 15,092
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.

Quote:
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".
__________________
Jesper
See my profile interests for Q&A
  Reply With Quote
Old January 10th, 2022, 02:06 PM   #13
George Graziano
Lifetime Supporting Member
United States

George Graziano is offline
 
Join Date: Jul 2004
Location: NY
Posts: 429
Jesper,
Not sure if I can paste a website. But it may be what you are looking for.
George
https://superuser.com/questions/3700...n-excel/370149
  Reply With Quote
Old January 10th, 2022, 02:18 PM   #14
saultgeorge
Lifetime Supporting Member
United States

saultgeorge is offline
 
saultgeorge's Avatar
 
Join Date: Jul 2015
Location: Detroit
Posts: 473
' 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
__________________
"Instant success teaches one nothing. Failure provides the tools and instructions one needs to succeed."
  Reply With Quote
Old January 10th, 2022, 08:07 PM   #15
James Mcquade
Member
United States

James Mcquade is offline
 
Join Date: Oct 2007
Location: Tennessee
Posts: 3,193
You may want to format the excel column to do word wrapping and expand the width / height of the cell. 1 line of text typed together can then show up as 5 of 6 lines of various widths.
james
  Reply With Quote
Reply
Jump to Live PLC Question and Answer Forum

Bookmarks


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Topics
Thread Thread Starter Forum Replies Last Post
Carriage return & string data types Tigger LIVE PLC Questions And Answers 3 August 19th, 2005 02:19 PM
A bit OT: Excel grief. JesperMP LIVE PLC Questions And Answers 26 July 27th, 2005 12:40 PM
OT, Excel "read only problem" elevmike LIVE PLC Questions And Answers 2 January 16th, 2005 02:38 PM
Calling all Siemens S7-200 gurus mjamil LIVE PLC Questions And Answers 14 October 17th, 2004 08:22 PM
Display String file on PanelView 1000 wa5yom LIVE PLC Questions And Answers 33 September 15th, 2003 12:53 PM


All times are GMT -4. The time now is 03:19 PM.


.