Formula Help Needed to Remove 0 From Results

Joyce Wong

Hello Smartsheet Community!

I need some help to better understand what is happening. Why are only some of my results generating a leading 0 in front of my text? And how can I get rid of the "0"? I appreciate any help I can get!

Here is an example of my formula:

=IF(([Plant Name (China)]@row + [Plant Name (Taiwan)]@row + [Plant Name (Singapore)]@row + [Plant Name (India)]@row + [Plant Name (Thailand)]@row + [Plant Name (Japan)]@row + [Plant Name (Korea)]@row + [Plant Name (Australia)]@row) > 0, ([Plant Name (China)]@row + [Plant Name (Taiwan)]@row + [Plant Name (Singapore)]@row + [Plant Name (India)]@row + [Plant Name (Thailand)]@row + [Plant Name (Japan)]@row + [Plant Name (Korea)]@row + [Plant Name (Australia)]@row), "")

=IF((Access@row + Catering@row + Cleaning@row + Comms@row + Drainage@row + DWS@row + Electrical@row + Fabric@row + [Fire & Sec]@row + Furnishing@row + HVAC@row + ICT@row + Leisure@row + [Medical ]@row + Plumbing@row + Safety@row + Rail@row + Transport@row + Technical@row + Utility@row + Site@row + Tools@row + [Fuel Distribution]@row) > 0, (Access@row + Catering@row + Cleaning@row + Comms@row + Drainage@row + DWS@row + Electrical@row + Fabric@row + [Fire & Sec]@row + Furnishing@row + HVAC@row + ICT@row + Leisure@row + [Medical ]@row + Plumbing@row + Safety@row + Rail@row + Transport@row + Technical@row + Utility@row + Site@row + Tools@row + [Fuel Distribution]@row), "")

Mark.poole

@Joyce Wong

Change the >0 in your formula to <>"". This means is not blank. the way you have it now it is searching for a numeric value.

=IF(([Plant Name (China)]@row + [Plant Name (Taiwan)]@row + [Plant Name (Singapore)]@row + [Plant Name (India)]@row + [Plant Name (Thailand)]@row + [Plant Name (Japan)]@row + [Plant Name (Korea)]@row + [Plant Name (Australia)]@row) <> "", ([Plant Name (China)]@row + [Plant Name (Taiwan)]@row + [Plant Name (Singapore)]@row + [Plant Name (India)]@row + [Plant Name (Thailand)]@row + [Plant Name (Japan)]@row + [Plant Name (Korea)]@row + [Plant Name (Australia)]@row), "")

=IF((Access@row + Catering@row + Cleaning@row + Comms@row + Drainage@row + DWS@row + Electrical@row + Fabric@row + [Fire & Sec]@row + Furnishing@row + HVAC@row + ICT@row + Leisure@row + [Medical ]@row + Plumbing@row + Safety@row + Rail@row + Transport@row + Technical@row + Utility@row + Site@row + Tools@row + [Fuel Distribution]@row) <> "", (Access@row + Catering@row + Cleaning@row + Comms@row + Drainage@row + DWS@row + Electrical@row + Fabric@row + [Fire & Sec]@row + Furnishing@row + HVAC@row + ICT@row + Leisure@row + [Medical ]@row + Plumbing@row + Safety@row + Rail@row + Transport@row + Technical@row + Utility@row + Site@row + Tools@row + [Fuel Distribution]@row), "")

Mark.poole

@Joyce Wong

Change the >0 in your formula to <>"". This means is not blank. the way you have it now it is searching for a numeric value.

=IF(([Plant Name (China)]@row + [Plant Name (Taiwan)]@row + [Plant Name (Singapore)]@row + [Plant Name (India)]@row + [Plant Name (Thailand)]@row + [Plant Name (Japan)]@row + [Plant Name (Korea)]@row + [Plant Name (Australia)]@row) <> "", ([Plant Name (China)]@row + [Plant Name (Taiwan)]@row + [Plant Name (Singapore)]@row + [Plant Name (India)]@row + [Plant Name (Thailand)]@row + [Plant Name (Japan)]@row + [Plant Name (Korea)]@row + [Plant Name (Australia)]@row), "")

=IF((Access@row + Catering@row + Cleaning@row + Comms@row + Drainage@row + DWS@row + Electrical@row + Fabric@row + [Fire & Sec]@row + Furnishing@row + HVAC@row + ICT@row + Leisure@row + [Medical ]@row + Plumbing@row + Safety@row + Rail@row + Transport@row + Technical@row + Utility@row + Site@row + Tools@row + [Fuel Distribution]@row) <> "", (Access@row + Catering@row + Cleaning@row + Comms@row + Drainage@row + DWS@row + Electrical@row + Fabric@row + [Fire & Sec]@row + Furnishing@row + HVAC@row + ICT@row + Leisure@row + [Medical ]@row + Plumbing@row + Safety@row + Rail@row + Transport@row + Technical@row + Utility@row + Site@row + Tools@row + [Fuel Distribution]@row), "")

Joyce Wong

Thank you so much! It works now