Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old August 28th 19, 07:46 PM
Junior Member
 
First recorded activity by ExcelBanter: Aug 2019
Posts: 5
Default 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   Report Post  
Old August 28th 19, 08:04 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,713
Default 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   Report Post  
Old August 29th 19, 03:31 PM
Junior Member
 
First recorded activity by ExcelBanter: Aug 2019
Posts: 5
Default

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:
Originally Posted by Claus Busch View Post
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
  #4   Report Post  
Old August 29th 19, 03:56 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,713
Default 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   Report Post  
Old August 29th 19, 04:20 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2019
Posts: 3
Default 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   Report Post  
Old August 29th 19, 05:49 PM
Junior Member
 
First recorded activity by ExcelBanter: Aug 2019
Posts: 5
Default

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

Thank you!

Quote:
Originally Posted by Claus Busch View Post
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
  #7   Report Post  
Old August 29th 19, 07:11 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,713
Default 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   Report Post  
Old August 30th 19, 08:26 PM
Junior Member
 
First recorded activity by ExcelBanter: Aug 2019
Posts: 5
Default

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!

Quote:
Originally Posted by Claus Busch View Post
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
  #9   Report Post  
Old August 30th 19, 08:28 PM
Junior Member
 
First recorded activity by ExcelBanter: Aug 2019
Posts: 5
Default

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:
Originally Posted by Benoît View Post
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
  #10   Report Post  
Old August 30th 19, 08:59 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,713
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
User Function Question: Collect Condition in Dialog Box - But How toInsert into Function Equation? SteveM Excel Programming 1 January 3rd 08 03:45 PM
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) [email protected] Excel Worksheet Functions 0 September 5th 06 03:44 PM
Excel - User Defined Function Error: This function takes no argume BruceInCalgary Excel Programming 3 August 23rd 06 08:53 PM
Offset function with nested match function not finding host ss. MKunert Excel Worksheet Functions 1 March 21st 06 10:46 PM
Emulate Index/Match combo function w/ VBA custom function Spencer Hutton Excel Worksheet Functions 2 May 2nd 05 05:26 PM


All times are GMT +1. The time now is 02:01 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright ©2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017