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 11th, 2022, 04:22 AM   #16
JesperMP
Lifetime Supporting Member + Moderator
Denmark

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

plvlce is offline
 
Join Date: May 2017
Location: Michigan
Posts: 406
Quote:
Originally Posted by JesperMP View Post
I have made a mistake in not explaining the problem properly. Wordwrap etc. has nothing to do with the problem.
Seemed pretty clear from post #6 to me...
  Reply With Quote
Old January 11th, 2022, 08:29 AM   #18
cjd1965
Lifetime Supporting Member
United Kingdom

cjd1965 is offline
 
Join Date: Apr 2007
Location: UK
Posts: 1,657
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
  Reply With Quote
Old January 11th, 2022, 09:03 AM   #19
JesperMP
Lifetime Supporting Member + Moderator
Denmark

JesperMP is offline
 
JesperMP's Avatar
 
Join Date: Feb 2003
Location: Copenhagen.
Posts: 15,098
Quote:
Originally Posted by cjd1965 View Post
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
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.
__________________
Jesper
See my profile interests for Q&A
  Reply With Quote
Old January 11th, 2022, 09:24 AM   #20
JesperMP
Lifetime Supporting Member + Moderator
Denmark

JesperMP is offline
 
JesperMP's Avatar
 
Join Date: Feb 2003
Location: Copenhagen.
Posts: 15,098
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.
Attached Images
File Type: png excel_frustration.png (22.7 KB, 90 views)
__________________
Jesper
See my profile interests for Q&A
  Reply With Quote
Old January 11th, 2022, 11:07 AM   #21
mylespetro
Supporting Member
Canada

mylespetro is offline
 
mylespetro's Avatar
 
Join Date: Dec 2015
Location: NS
Posts: 519
Quote:
Originally Posted by JesperMP View Post
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.
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:
Attached Images
File Type: jpg ExcelFind.JPG (84.8 KB, 88 views)

Last edited by mylespetro; January 11th, 2022 at 11:09 AM.
  Reply With Quote
Old January 11th, 2022, 07:33 PM   #22
JaxGTO
Member
United States

JaxGTO is offline
 
JaxGTO's Avatar
 
Join Date: Apr 2009
Location: Kalieefornia
Posts: 949
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 have to use Rockwell Software!
  Reply With Quote
Old January 12th, 2022, 01:24 PM   #23
cjd1965
Lifetime Supporting Member
United Kingdom

cjd1965 is offline
 
Join Date: Apr 2007
Location: UK
Posts: 1,657
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
Code:
=A1 & CHAR(10) & B2 & CHAR(10) & C1
Then set D1 format to wordwrap

D1 contains the new message
  Reply With Quote
Old January 12th, 2022, 01:45 PM   #24
L33er
Member
United States

L33er is offline
 
L33er's Avatar
 
Join Date: Feb 2021
Location: North
Posts: 83
Works fine for me!



Possibly, he is using a counterfeit copy of excel.
  Reply With Quote
Old January 13th, 2022, 04:29 AM   #25
cjd1965
Lifetime Supporting Member
United Kingdom

cjd1965 is offline
 
Join Date: Apr 2007
Location: UK
Posts: 1,657
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
  Reply With Quote
Old January 13th, 2022, 05:51 AM   #26
JesperMP
Lifetime Supporting Member + Moderator
Denmark

JesperMP is offline
 
JesperMP's Avatar
 
Join Date: Feb 2003
Location: Copenhagen.
Posts: 15,098
I will look at this again when I get to the next project.
__________________
Jesper
See my profile interests for Q&A
  Reply With Quote
Old January 14th, 2022, 04:31 PM   #27
Lare
Member
Finland

Lare is offline
 
Join Date: Jan 2006
Location: Finland
Posts: 1,740
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.

Last edited by Lare; January 14th, 2022 at 04:34 PM.
  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 01:54 AM.


.