Remove comma if no data found

How do I modify the following to handle if the results only return a comma, to remove the comma.

=VLOOKUP(MainFormTbl[Company Name],BillingInfo[#All],5,FALSE) & ", " &VLOOKUP(MainFormTbl[Company Name],BillingInfo[#All],6,FALSE) & "  " &VLOOKUP(MainFormTbl[Company Name],BillingInfo[#All],7,FALSE)

results = City, ST  ZipCode
Karen SchaeferBI ANALYSTAsked:
Who is Participating?
 
Ryan ChongConnect With a Mentor Commented:
also can try:
=TRIM( IF( VLOOKUP(MainFormTbl[Company Name],BillingInfo[#All],5,FALSE) = "" , "", VLOOKUP(MainFormTbl[Company Name],BillingInfo[#All],5,FALSE) & ", " ) & VLOOKUP(MainFormTbl[Company Name],BillingInfo[#All],6,FALSE) & "  " &VLOOKUP(MainFormTbl[Company Name],BillingInfo[#All],7,FALSE) )

Open in new window

0
 
Phil DavidsonCommented:
I would try this:

=IF(LEFT(A1,2)=",",SUBSTITUTE(A1,",",""),IF(RIGHT(A1,2)="--",SUBSTITUTE(A1,",",""), A1))

I got the idea from one of the answers here.  If a comma is the right most character and left most character, one comma will be replaced with nothing.
0
 
Alexandre MichelConnect With a Mentor Manager; IT ConsultantCommented:
you can use this formula

IF ( X = "," , "" , X )

if X is a comma return nothing otherwise return X itself


replace X with VLOOKUP(MainFormTbl[Company Name],BillingInfo[#All],5,FALSE) & ", " &VLOOKUP(MainFormTbl[Company Name],BillingInfo[#All],6,FALSE) & "  " &VLOOKUP(MainFormTbl[Company Name],BillingInfo[#All],7,FALSE)

and it becomes

IF ( VLOOKUP(MainFormTbl[Company Name],BillingInfo[#All],5,FALSE) & ", " &VLOOKUP(MainFormTbl[Company Name],BillingInfo[#All],6,FALSE) & "  " &VLOOKUP(MainFormTbl[Company Name],BillingInfo[#All],7,FALSE) = "," , "" , VLOOKUP(MainFormTbl[Company Name],BillingInfo[#All],5,FALSE) & ", " &VLOOKUP(MainFormTbl[Company Name],BillingInfo[#All],6,FALSE) & "  " &VLOOKUP(MainFormTbl[Company Name],BillingInfo[#All],7,FALSE) )

Alex
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
thanks for the great suggestion.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.