• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 43
  • Last Modified:

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
0
Karen Schaefer
Asked:
Karen Schaefer
2 Solutions
 
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
 
Ryan ChongCommented:
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
 
Alexandre MichelManager; 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 SchaeferAuthor Commented:
thanks for the great suggestion.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now