Pages in topic:   [1 2] >
Is there an easy and fast way to enter prescribed number of characters in a MS Excel file?
Thread poster: Rajan Chopra
Rajan Chopra
Rajan Chopra
India
Local time: 07:11
Member (2008)
English to Hindi
+ ...
Mar 31, 2020

Hi experts,

I have MS Office 2007 and Trados Studio 2015.

I am working on a big project in Excel wherein I have to enter the translation in the form of a maximum of 32 characters in each of the two cells against each and every source cell. The text is to be used as subtitles. This is a very tedious and time consuming process because a sentence is generally broken into 4-5 cells and since the syntax of English and Hindi is different, I can't enter matching translatio
... See more
Hi experts,

I have MS Office 2007 and Trados Studio 2015.

I am working on a big project in Excel wherein I have to enter the translation in the form of a maximum of 32 characters in each of the two cells against each and every source cell. The text is to be used as subtitles. This is a very tedious and time consuming process because a sentence is generally broken into 4-5 cells and since the syntax of English and Hindi is different, I can't enter matching translation in each and every cell. As a result, I pasted the source in a tabulated Word file, joined all broken sentences together and then translated them into Hindi. It is still very problematic to cut the exact number of characters from the Word file and then paste into Excel file because I have to frequently adjust the additional or less characters.

Does a convenient and fast method exist to do it?

Thanks and regards,

Chopra
Collapse


 
Gerard de Noord
Gerard de Noord  Identity Verified
France
Local time: 02:41
Member (2003)
English to Dutch
+ ...
=LEN Mar 31, 2020

Hi Chopra,

I'm not an Excel expert but I use the LEN function for that purpose.

You can type =LEN(A1) in e.g. cell A3 to show the number of characters in cell A1 in cell A3.

You can Ctrl+drag down from the bottom right corner of cell A3 to perform a character count in all cells of column 1.

Cheers,
Gerard


Rajan Chopra
 
Joakim Braun
Joakim Braun  Identity Verified
Sweden
Local time: 02:41
German to Swedish
+ ...
Conditional formatting Mar 31, 2020

You could have an adjacent cell showing the character count (LEN() formula).

You could also use conditional formatting to turn the text red if > 32 characters.
(Classic style, rula formula "=LEN(A1)>32" and use a hard coded "ap
... See more
You could have an adjacent cell showing the character count (LEN() formula).

You could also use conditional formatting to turn the text red if > 32 characters.
(Classic style, rula formula "=LEN(A1)>32" and use a hard coded "applies to" cell reference, see https://exceljet.net/lessons/how-to-check-line-length-with-conditional-formatting.)

[Bearbeitet am 2020-03-31 11:57 GMT]
Collapse


Rajan Chopra
 
Rolf Keller
Rolf Keller
Germany
Local time: 02:41
English to German
Use a macro or a plain-text editor Mar 31, 2020

You could use an Office macro but you need a friendly person who creates it for you.

Another solution: Copy all the text from Word into a plain-text editor that features a "Wordwrap = off" setting. Then set the line length resp the windows size to 32 chars.
The Windows editor is unsuitable, the free Notepad++ might work (I don't know). The free Visual Studio Code editor works but requires training. Maybe a colleague can propose a suitable editor.


Rajan Chopra
 
Rajan Chopra
Rajan Chopra
India
Local time: 07:11
Member (2008)
English to Hindi
+ ...
TOPIC STARTER
Yes, the text already .... Mar 31, 2020

turns red whenever the character limit (32) is exceeded which means that they have already applied this feature. But I am cutting the text from a Word file and then pasting it into the Excel file. I don't know how many characters that part contains and I get to know it, when I paste the text in Excel file and then I have to cut the additional text or add the less text which is causing trouble.


Joakim Braun wrote:

You could have an adjacent cell showing the character count (LEN() formula).

You could also use conditional formatting to turn the text red if > 32 characters.
(Classic style, rula formula "=LEN(A1)>32" and use a hard coded "applies to" cell reference, see https://exceljet.net/lessons/how-to-check-line-length-with-conditional-formatting.)

[Bearbeitet am 2020-03-31 11:57 GMT]


[Edited at 2020-03-31 17:06 GMT]


 
Rajan Chopra
Rajan Chopra
India
Local time: 07:11
Member (2008)
English to Hindi
+ ...
TOPIC STARTER
This seems to be an excellent suggestion. But... Mar 31, 2020

I pasted the entire text in Notepad and wrapped it but there is no feature or command to define the line length as 32 characters. Thanks for the valuable advice, though. Some other software might do it.

Rolf Keller wrote:


Another solution: Copy all the text from Word into a plain-text editor that features a "Wordwrap = off" setting. Then set the line length resp the windows size to 32 chars.
The Windows editor is unsuitable, the free Notepad++ might work (I don't know). The free Visual Studio Code editor works but requires training. Maybe a colleague can propose a suitable editor.



[Edited at 2020-03-31 17:24 GMT]


 
Joakim Braun
Joakim Braun  Identity Verified
Sweden
Local time: 02:41
German to Swedish
+ ...
One more solution Mar 31, 2020

https://stackoverflow.com/questions/43249900/excel-vba-how-to-insert-a-character-in-a-string-every-n-characters?rq=1
(you could insert some arbitrary string, then copy/paste to a text editor and change it to a line break, then paste back into Excel)


[Bearbeitet am 2020-03-31 17:19 GMT]


 
Joakim Braun
Joakim Braun  Identity Verified
Sweden
Local time: 02:41
German to Swedish
+ ...
And one more Mar 31, 2020

(Can't paste two links in same post apparently)

https://stackoverflow.com/questions/49850210/excel-formula-to-split-cell-text-based-on-char-count
Not quite what you want, but a starting point.

[Bearbeitet am 2020-03-31 17:19 GMT]


 
Stepan Konev
Stepan Konev  Identity Verified
Russian Federation
Local time: 04:41
English to Russian
AHK script Mar 31, 2020

chopra_2002 wrote:
I am cutting the text from a Word file and then pasting it into the Excel file. I don't know how many characters that part contains and I get to know it, when I paste the text in Excel file

As far as I understand you want to know the number of characters in MS Word before you paste it into Excel. If so, select a fragment and then press Ctrl+Shift+G.

Just in case you need to solve this problem for other text editors (Excel, browser, Notepad, CAT tool, etc.), you can use the following AHK script:
--------------------------------------------------------------------
#c::
{
Send, ^c
StringReplace, OutputVar, ClipBoard, %A_Space%,, All UseErrorLevel
Length := StrLen(Clipboard)
MsgBox % "Spaces: " ErrorLevel "`nChars: " (Length-ErrorLevel) "`nTotal: "(length) "`nLine: " Clipboard
}
return
--------------------------------------------------------------------
Once you run this script, select any portion of text and press Win+C. This will show you the number of chars in your selection (spaces, non-space chars, total, and the selection itself).

[Edited at 2020-04-01 01:50 GMT]


Rajan Chopra
 
Stepan Konev
Stepan Konev  Identity Verified
Russian Federation
Local time: 04:41
English to Russian
AHK script (continued) Mar 31, 2020

Update: I have just found out this script won't work for Hindi, because this language contains multi-character symbols. However it is still valid for the languages that use 'regular' alphabet.

This AHK script selects 32 chars from cursor by pressing F12.
--------------------------------------------------------------------
F12::
Send,{Shift down}{Right 32}{Shift up}
Send, ^c
Return
-------------------------------------------------------------------
... See more
Update: I have just found out this script won't work for Hindi, because this language contains multi-character symbols. However it is still valid for the languages that use 'regular' alphabet.

This AHK script selects 32 chars from cursor by pressing F12.
--------------------------------------------------------------------
F12::
Send,{Shift down}{Right 32}{Shift up}
Send, ^c
Return
--------------------------------------------------------------------
I am not sure how it behaves with right-to-left languages, but in any case you can change {Right 32} to {Left 32} to make the script count characters from the right to the left.
Also you can set the number of characters to select by adjusting 32 to any other number.
Also you can change the actuating button (F12), but you need to know the basic AHK rules for that.
For example:
#v = Win+V
!v = Alt+V
^v = Ctrl+V
+v = Shift+V
^!v = Ctrl+Alt+V
etc.

[Edited at 2020-04-01 03:49 GMT]
Collapse


 
Giuliana Maltempo
Giuliana Maltempo
Argentina
Local time: 22:41
Member (2023)
English to Spanish
+ ...
Monospaced font Apr 1, 2020

A simple and fast solution that does not require any scripting: use a monospaced font. You can adjust the width of the column to exacly 32 characters. Then, if you exceed the limit, your text will break to a new line. It's pretty visual.

 
Rolf Keller
Rolf Keller
Germany
Local time: 02:41
English to German
Notepad++ vs. Notepad Apr 1, 2020

chopra_2002 wrote:

I pasted the entire text in Notepad and wrapped it but there is no feature or command to define the line length as 32 characters.

Notepad is the Windows editor. As I said, it is not suitable here. I said "Notepad++" which is a different piece of software.

FWIW: You want to cut the full Word document into pieces of 32 characters each, don't you? If so, most of the answerers need a more clear explanation because they seem to think of a different task.


Rajan Chopra
 
Rajan Chopra
Rajan Chopra
India
Local time: 07:11
Member (2008)
English to Hindi
+ ...
TOPIC STARTER
Simple method Apr 1, 2020

Yes, it is a simple method to use for a layman like me. Thanks.

[quote]Stepan Konev wrote:

chopra_2002 wrote:

As far as I understand you want to know the number of characters in MS Word before you paste it into Excel. If so, select a fragment and then press Ctrl+Shift+G.



 
Rajan Chopra
Rajan Chopra
India
Local time: 07:11
Member (2008)
English to Hindi
+ ...
TOPIC STARTER
Exactly Apr 1, 2020

Rolf Keller wrote:

FWIW: You want to cut the full Word document into pieces of 32 characters each, don't you? If so, most of the answerers need a more clear explanation because they seem to think of a different task.


Yes, this is precisely what I want because in that case I won't have need to divide the text manually into 32 characters each segments.

But I apprehend that it might cut long words when it reaches the 32 characters limit and those words will be incomplete.

[Edited at 2020-04-01 08:53 GMT]


 
Gerard de Noord
Gerard de Noord  Identity Verified
France
Local time: 02:41
Member (2003)
English to Dutch
+ ...
Quick and dirty Apr 1, 2020

I think Giuliana came up with the easiest solution. Use a monospaced Hindi font in Word and move the right margin of the whole document to the left until the first line starts to wrap.

Screenshot (15)


Rajan Chopra
 
Pages in topic:   [1 2] >


To report site rules violations or get help, contact a site moderator:


You can also contact site staff by submitting a support request »

Is there an easy and fast way to enter prescribed number of characters in a MS Excel file?






CafeTran Espresso
You've never met a CAT tool this clever!

Translate faster & easier, using a sophisticated CAT tool built by a translator / developer. Accept jobs from clients who use Trados, MemoQ, Wordfast & major CAT tools. Download and start using CafeTran Espresso -- for free

Buy now! »
Anycount & Translation Office 3000
Translation Office 3000

Translation Office 3000 is an advanced accounting tool for freelance translators and small agencies. TO3000 easily and seamlessly integrates with the business life of professional freelance translators.

More info »