Home 
Search 
Today's Posts 
#1




IF function + NBCAR
I would like to have a formula that only applies to a cell if another has been filled beforehand.
So cells in the column G might contain a text. Colum N has to show the number of characters that column G contains (=LEN) but I don't want column N to contain the number 0 if column G has no text. I would rather have column N blank. The first row 1 does not contain data, it's the titles. And all the following rows are the data. So I had =LEN(G2) for every N cell row with their corresponding G cell number. Also, I work with the French version of Excel and so I can find the equivalents for the terms but I don't if the punctuation changes across languages... does it? Can I ask questions about macros in here or not really? Thank you! Andréa Last edited by Andrea P : August 28th 19 at 07:51 PM 
#2




IF function + NBCAR
Hi Andrea,
Am Wed, 28 Aug 2019 19:46:12 +0100 schrieb Andrea P: I would like to have a formula that only applies to a cell if another has been filled beforehand. So cells in the column G might contain a text. Colum N has to show the number of characters that column G contains (=LEN) but I don't want column N to contain the number 0 if column G has no text. I would rather have column N blank. So for example: /// A..B..C...G......................................N 1.....................asdaljda.................... ...8 2............................................................. 3....................sdjf......................... .........4 try: =SI(NBCAR(G1)0;NBCAR(G1);"") You can post questions about VBA in microsoft.public.excel.programming Regards Claus B.  Windows10 Office 2016 
#3




Hello!
Thank you so much for your fast answer. I got an error message saying the formula is incoherent. It proposed me to add an asterisk (*) as follows: =SI(NBCAR(G70)*0;NBCAR(G70);"") But it still does not work. I want N to show the number of characters that G contains (if it contains any). Thank you! Quote:

#4




IF function + NBCAR
Hi Andrea,
Am Thu, 29 Aug 2019 15:31:48 +0100 schrieb Andrea P: I got an error message saying the formula is incoherent. It proposed me to add an asterisk (*) as follows: =SI(NBCAR(G70)*0;NBCAR(G70);"") my posted formula should work. I guess the issue is caused by the separators. I don't know what separators are used in a french system. Try my formula and change the semicolon to comma. You could use only =NBCAR(G70) and deactivate "In cells with zero values show 0" in the options. Regards Claus B.  Windows10 Office 2016 
#5




IF function + NBCAR
Claus Busch wrote:
Hi Andrea, Am Thu, 29 Aug 2019 15:31:48 +0100 schrieb Andrea P: I got an error message saying the formula is incoherent. It proposed me to add an asterisk (*) as follows: =SI(NBCAR(G70)*0;NBCAR(G70);"") my posted formula should work. I guess the issue is caused by the separators. I don't know what separators are used in a french system. Try my formula and change the semicolon to comma. You could use only =NBCAR(G70) and deactivate "In cells with zero values show 0" in the options. Since SI = IF there has to be a « reason if » so I would write: =SI(NBCAR(G70)=0;NBCAR(G70);"") ^^^ It's in fact (IF;THEN;ELSE)  Vie : n.f. maladie mortelle sexuellement transmissible Benoit chez lui à leraillez.com 
#6




How do you do deactivate "In cells with zero values show 0" in the options? I can't find it...
Thank you! Quote:

#7




IF function + NBCAR
Hi Andrea,
Am Thu, 29 Aug 2019 17:49:51 +0100 schrieb Andrea P: How do you do deactivate "In cells with zero values show 0" in the options? I can't find it... Options = Advanced = Display options for this worksheet Regards Claus B.  Windows10 Office 2016 
#8




Unfortunately, I need those cells to be blank because I'm making an macro that updates the sheet. When I press Ctrl+A, it usually only selects the actual used range. But with the option to not show the zeros, the cells are still filled so I have a very big range selected which slows the macro down.
Help! 
#9




Hi Benoit,
For some reason, it also doesn't work... I have a message saying that the formula is incoherent. The N cell is indeed blank but it doesn't show any number when the G column does have characters in it. Help! Quote:

#10




IF function + NBCAR
Hi Andrea,
Am Fri, 30 Aug 2019 20:26:59 +0100 schrieb Andrea P: Unfortunately, I need those cells to be blank because I'm making an macro that updates the sheet. When I press Ctrl+A, it usually only selects the actual used range. But with the option to not show the zeros, the cells are still filled so I have a very big range selected which slows the macro down. download the file from he https://1drv.ms/x/s!AqMiGBK2qniTgeV9...sVDLg?e=QJRpUh When you open it on your system, the formulas should be translated to French. Regards Claus B.  Windows10 Office 2016 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
User Function Question: Collect Condition in Dialog Box  But How toInsert into Function Equation?  Excel Programming  
LINKEDRANGE function  a complement to the PULL function (for getting values from a closed workbook)  Excel Worksheet Functions  
Excel  User Defined Function Error: This function takes no argume  Excel Programming  
Offset function with nested match function not finding host ss.  Excel Worksheet Functions  
Emulate Index/Match combo function w/ VBA custom function  Excel Worksheet Functions 