How to count characters on multiline cells on Ms Excel?
Thread poster: Elizabeth Sánchez León

Elizabeth Sánchez León  Identity Verified
Spain
Local time: 23:12
English to Spanish
+ ...
Sep 4, 2008

I have been searching for this topic and I have not found much information.

I have an .xsl file with cells having severals lines on each one. Each cell has a different character limitation, both in the no. of accepted lines and in the no. of accepted characters per line. Does anyone know an efficient way to count both the lines and the characters per line of each cell? I know how to use the LENGTH function of Ms Excel, but I think here I need something more sophisticated.

Many thanks for your help!


Direct link Reply with quote
 

Antoní­n Otáhal
Local time: 00:12
Member (2005)
English to Czech
+ ...
Some programming is needed Sep 4, 2008

The InStrRev function of Visual Basic will come handy (looking up the positions of manual line break "control character" within cell strings):

Description

Returns the position of an occurrence of one string within another, from the end of string.

Syntax

InstrRev(stringcheck, stringmatch[, start[, compare]])


You will find more info in Visual Basic help.

If I were programming it, I would probably export to Word, or txt or xml, before working further with it - but most likely it can be done even in Excel itself. It mostly depends on your personal taste.

HTH

Antonin

[Edited at 2008-09-04 19:29]


Direct link Reply with quote
 
raptisi  Identity Verified
Spain
Local time: 01:12
English to Greek
+ ...
Cut&Paste Sep 5, 2008

I am not sure if that is exactly what you are asking, but you could copy the Excel content into a Word document and then do the word count there.
Just a suggestion!

[Editado a las 2008-09-05 10:20]


Direct link Reply with quote
 
Ryan Ginstrom
Local time: 07:12
Japanese to English
Using VBA to get the number of lines in an Excel cell Sep 5, 2008

Does anyone know an efficient way to count both the lines and the characters per line of each cell? I know how to use the LENGTH function of Ms Excel, but I think here I need something more sophisticated.


To get the number of lines, you could split the cell's text on the newline character (10).

Lines = Split(Application.ActiveCell.Text, Chr(10))
' array is zero based, so length will be ubound + 1
Debug.Print Str(UBound(Lines) + 1) + " lines"

length will get you the number of characters.

HTH!

[Edited at 2008-09-06 04:22]


Direct link Reply with quote
 


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


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

How to count characters on multiline cells on Ms Excel?

Advanced search






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 »
BaccS – Business Accounting Software
Modern desktop project management for freelance translators

BaccS makes it easy for translators to manage their projects, schedule tasks, create invoices, and view highly customizable reports. User-friendly, ProZ.com integration, community-driven development – a few reasons BaccS is trusted by translators!

More info »



Forums
  • All of ProZ.com
  • Term search
  • Jobs
  • Forums
  • Multiple search