Technical forums » Office applications »
Create a warning in Excel (characters restrictions)
Thread poster: VIELLES Ophélie
VIELLES Ophélie
VIELLES Ophélie  Identity Verified
France
Local time: 20:59
Member (2012)
English to French
+ ...
Nov 20, 2018

Hi!
I'd like to create an automatic "script" in an Excel column that would count characters (such as NBCAR) but also display a warning when the limit is exceeded. (each string have a different limitation)
For instance :
Column A : source text
Column C : characters limitations (specific for each row) for text in column A
Column D : warning if source text (in column A) exceeds the character limit (specified in column C)

Is there any way to create an auto... See more
Hi!
I'd like to create an automatic "script" in an Excel column that would count characters (such as NBCAR) but also display a warning when the limit is exceeded. (each string have a different limitation)
For instance :
Column A : source text
Column C : characters limitations (specific for each row) for text in column A
Column D : warning if source text (in column A) exceeds the character limit (specified in column C)

Is there any way to create an automatic warning to achieve this?

Thank you so much for your kind help!

Ophélie ▲ Collapse
 
Michael Davies
Michael Davies  Identity Verified
Denmark
Local time: 20:59
Member (2009)
Danish to English
+ ...
Automatic warning of excessive length Nov 20, 2018

Bonjour Ophélie,

You could use the formula: =IF(LEN(+A4)>+B4; "WARNING"; "") in column C to display ,e.g., the text 'WARNING' if the length of the text exceeds the max. string length specified in column B.

Cordialement,

Michael
 
Thomas T. Frost
Thomas T. Frost  Identity Verified
Portugal
Local time: 19:59
Danish to English
+ ...
Also conditional formatting Nov 20, 2018

Michael has given you the answer, although the + signs are redundant (they have no function).

An alternative is to use Conditional Formatting to change the colour of the offending cells to red (or another formatting). This eliminates the need for a separate warning column. See the screenshot.

Capture cond

Note that if you need to update the formula, press F2 first.

Office support article:
https://support.office.com/en-us/article/use-formulas-with-conditional-formatting-fed60dfa-1d3f-4e13-9ecb-f1951ff89d7f
 
VIELLES Ophélie
VIELLES Ophélie  Identity Verified
France
Local time: 20:59
Member (2012)
English to French
+ ...
TOPIC STARTER
Thank you! Nov 20, 2018

Thanks to both of you, I'll give it a try this evening (as soon as my file is ready) and let you know if it works!
 
Stepan Konev
Stepan Konev  Identity Verified
Russian Federation
Local time: 21:59
English to Russian
Just minor remarks Nov 20, 2018

1. I guess it should be A1>B1 if I start from line 1, right?
2. If you use a CAT tool for translation, the formulas would not be updated automatically. As far as I know F2 only works for one cell at a time. To update all formulas you should use Alt+Shift+Ctrl+F9.
 
VIELLES Ophélie
VIELLES Ophélie  Identity Verified
France
Local time: 20:59
Member (2012)
English to French
+ ...
TOPIC STARTER
thank you, it works like a charm Nov 22, 2018

Thanks to all of you for your kind help, the formula worked like a charm. I haven't tweaked it without the + sign though, but I'll give it a try later. I guess this post will be useful to other translators....
By the way, I'm also curious to understand the difference between using =IF(LEN and =NBCAR (suggested by one of my colleague)? (with a red warning as well). Is one solution better than the other one? Thanks again!
 
Thomas T. Frost
Thomas T. Frost  Identity Verified
Portugal
Local time: 19:59
Danish to English
+ ...
Plus and NBCHAR Nov 22, 2018

VIELLES Ophélie wrote:

I haven't tweaked it without the + sign though, but I'll give it a try later. I guess this post will be useful to other translators....
By the way, I'm also curious to understand the difference between using =IF(LEN and =NBCAR (suggested by one of my colleague)? (with a red warning as well). Is one solution better than the other one? Thanks again!


My understanding is that the only function of the plus sign here is for Excel to accept this legacy notation from other software. The plus sign used this way has no function in Excel.

Excel does not recognise any NBCHAR function. I guess this is the name in some other software.
 


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

Moderator(s) of this forum
Maya Gorgoshidze[Call to this topic]
Prachya Mruetusatorn[Call to this topic]

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

Create a warning in Excel (characters restrictions)






Protemos translation business management system
Create your account in minutes, and start working! 3-month trial for agencies, and free for freelancers!

The system lets you keep client/vendor database, with contacts and rates, manage projects and assign jobs to vendors, issue invoices, track payments, store and manage project files, generate business reports on turnover profit per client/manager etc.

More info »
Wordfast Pro
Translation Memory Software for Any Platform

Exclusive discount for ProZ.com users! Save over 13% when purchasing Wordfast Pro through ProZ.com. Wordfast is the world's #1 provider of platform-independent Translation Memory software. Consistently ranked the most user-friendly and highest value

Buy now! »