Remove Null values from Concatenate string

Karen Schaefer
Karen Schaefer used Ask the Experts™
on
Handling NuLL in a concatenated statement.

How do I remove any values containing "NULL".

I am trying to concatenate the Type and size into a single value, however, some of the size = "Null".

I want to remove the Null from the concatenated values.

TYPE       Size          Final value
varchar      8      NO      varchar(8)
varchar      10      NO      varchar(10)
datetime      NULL      NO      datetime(NULL)
varchar      40      YES      varchar(40)
varchar      50      YES      varchar(50)
varchar      25      YES      varchar(25)
varchar      25      YES      varchar(25)
varchar      17      YES      varchar(17)
varchar      50      YES      varchar(50)
varchar      40      YES      varchar(40)
varchar      40      YES      varchar(40)
varchar      10      YES      varchar(10)
varchar      36      YES      varchar(36)
varchar      36      YES      varchar(36)
datetime      NULL      YES      datetime(NULL)
datetime      NULL      YES      datetime(NULL)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
Assuming Type is in A2 and Size in B2, try this...

=A2&IF(B2="NULL","","("&B2&")")

Open in new window

OR
=SUBSTITUTE(A2&"("&B2&")","(NULL)","")

Open in new window

Karen SchaeferBI ANALYST

Author

Commented:
=SUBSTITUTE(A2&"("&B2&")","(NULL)","")  Did the Trick.

Thanks,
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome Karen! Glad it worked as desired.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial