Microsoft Excel
--
Questions
--
Followers
Top Experts
Concatenate returning 0
I am concatenating 3 or so fields together but when one of them is blank it returns 0. Is there a way for it to not display 0 but still concatenate so when the field is populated data displays as normal?
Cheers.
Cheers.
Zero AI Policy
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
SOLUTION
membership
Log in or create a free account to see answer.
Signing up is free and takes 30 seconds. No credit card required.
Hi,
I just used A1&B1&C1 and for the blank cells I didn't get the 0 come through, can we see your formula or code?
I just used A1&B1&C1 and for the blank cells I didn't get the 0 come through, can we see your formula or code?
Still returns 0. Please note the 0 on the end should not be there. G60 is empty.
concatenate.jpg
concatenate.jpg
Can you show us your formula please?
I suspect that the cell (G60) isn't empty. It may appear empty because of the format of the cell, or because you have unticked the option to show zero values. Click on G60 and then look at what value appears in the formula bar. It should either display zero or a formula, and that formula is likely to return zero.
I suspect that the cell (G60) isn't empty. It may appear empty because of the format of the cell, or because you have unticked the option to show zero values. Click on G60 and then look at what value appears in the formula bar. It should either display zero or a formula, and that formula is likely to return zero.
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
Tested Mike's solution. Works in Excel 2007
Cell G60 is =Form!D9 and the cell on the sheet form which is D9 is empty. This may explain the presence of 0 because no doubt the formula is returning 0 as Cell D9 on the form sheet is empty.
Is there a way that "=Form!D9" can be changed so that the formula does not return 0 if cell D9 on the form sheet is blank?
Thanks.
Is there a way that "=Form!D9" can be changed so that the formula does not return 0 if cell D9 on the form sheet is blank?
Thanks.
ASKER CERTIFIED SOLUTION
membership
Log in or create a free account to see answer.
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
membership
Log in or create a free account to see answer.
Signing up is free and takes 30 seconds. No credit card required.
Using a combination of the above suggestions and finally =IF(Form!D9=0,"",Form!D9) solved this issue.
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
Microsoft Excel
--
Questions
--
Followers
Top Experts
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.