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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |