Concatenating with blanks

[VSCarter]

I have searched the forums for this specific question but was unable to find a similar situation.

Columns A through E all contain possible data entries, though some may not. In MOST cases, A will always have an entry. B through E might, but there would never be a case where B & C would be blank, and D would have something.

Column F needs to be a concatenate formula of all cells A through E.

The tricky part (for me!) is that each cell needs to be separated with the character "@".

I have the concatenate formula down -- but what I can't figure out is how to edit the formula so that if a cell (A-E) is blank, it won't return the "@".

So, if each cell value right now contain fruit names it would look like this:

Orange@Apple@Pear@Banana@Mango

However, if the cells are blank, I get this:

Orange@Apple@@@

How do I get the formula to ignore the blank cells and NOT add the "@"?

Thank you in advance.

I should add that the "@" isn't part of the original data entry, and is only a part of the concatenate formula.

 

[barry houdini]

Hello VSCarter,

When I tried to post a suggestion here the @s in the formula were interpreted as email "at" symbols and the text was hyperlinked so here I've use ^ in the formula instead of @

Try this in F2

=SUBSTITUTE(IF(A2="", "","^"&A2)&IF(B2="","","^"&B2)&IF(C2="","","^"&C2)&IF(D2="","","^"&D2)&IF(E2="","","^"&E2),"^","",1)

 

[stanleydgromjr]

VSCarter,

With Barry's fromula (great stuff as always), and my Function.

Excel Workbook
	A	B	C	D	E	F	G
1	Orange	Apple	Pear	Banana	Mango	Orange@Apple@Pear@Banana@Mango	Orange@Apple@Pear@Banana@Mango
2		Apple	Pear	Banana	Mango	Apple@Pear@Banana@Mango	Apple@Pear@Banana@Mango
3			Pear	Banana	Mango	Pear@Banana@Mango	Pear@Banana@Mango
4				Banana	Mango	Banana@Mango	Banana@Mango
5					Mango	Mango	Mango
6
7	Orange	Apple	Pear	Banana	Mango	Orange@Apple@Pear@Banana@Mango	Orange@Apple@Pear@Banana@Mango
8	Orange	Apple	Pear	Banana		Orange@Apple@Pear@Banana	Orange@Apple@Pear@Banana
9	Orange	Apple	Pear			Orange@Apple@Pear	Orange@Apple@Pear
10	Orange	Apple				Orange@Apple	Orange@Apple
11	Orange					Orange	Orange
12	Orange					Orange	Orange
13			Pear		Mango	Pear@Mango	Pear@Mango
14	Orange	Apple		Banana		Orange@Apple@Banana	Orange@Apple@Banana
Sheet1

Option Explicit
Function ConCat(MyRow As Long) As String
  Dim Hold As String
  Application.Volatile
  If Cells(MyRow, "A").Value <> "" Then Hold = Hold & Cells(MyRow, "A").Value & "@"
  If Cells(MyRow, "B").Value <> "" Then Hold = Hold & Cells(MyRow, "B").Value & "@"
  If Cells(MyRow, "C").Value <> "" Then Hold = Hold & Cells(MyRow, "C").Value & "@"
  If Cells(MyRow, "D").Value <> "" Then Hold = Hold & Cells(MyRow, "D").Value & "@"
  If Cells(MyRow, "E").Value <> "" Then Hold = Hold & Cells(MyRow, "E").Value & "@"
  If Right(Hold, 1) = "@" Then
    Hold = Mid(Hold, 1, Len(Hold) - 1)
  End If
  ConCat = Hold
End Function

The formula in cells F1 and G1 copied down:
F1
=ConCat(ROW())

G1
=SUBSTITUTE(IF(A1="", "","@"&A1)&IF(B1="","","@"&B1)&IF(C1="","","@"&C1)&IF(D1="","","@"&D1)&IF(E1="","","@"&E1),"@","",1)


Have a great day,
Stan

 

[Ron Coderre]

Based on your rules that:
• Col_A will ALWAYS have a value
• Subsequent values will continue from left to right...with no blanks in between

Then
For cells A2:E2
Try this:

F2: =LEFT(A2&"@"&B2&"@"&C2&"@"&D2&"@"&E2,SUMPRODUCT(LEN(A2:E2))+COUNTA(A2:E2)-1)

Does that help?

 

[VSCarter]

Ron,

For your posted formula (which seemed to be the easiest for me to understand), when I use that, AND have values in A, B and C, but nothing in D and E, here's what I get:

A@B@C@@@

I want it to only be:

A@B@C

I'm not sure how to fix it.

Thanks everyone for your input. These boards are tremendously helpful for me.

I should also point out -- I know NOTHING about functions, so answers with functions in them really confuse me. Yes, it's probably something I should learn very soon. :P

 

[barry houdini]

Ron's formula works for me if you only have values in columns A to C, perhaps there's something (a space?) in D and E. What's in those cells, do you have formulas?

 

[VSCarter]

Barry Houdini's formula works perfectly.

@Barry -- that's weird! I don't think there's anything in those cells. Let me go check.

 

[Ron Coderre]

Hmmm...that would imply that the blank cells probably have formulas in them that return an empty string: ""
Which is NOT blank.

If that's the case...try this:

F2: =LEFT(A1&"@"&B1&"@"&C1&"@"&D1&"@"&E1,SUMPRODUCT(LEN(A1:E1))+
COUNTIF(A1:E1,"?*")-1)

Does that solve the problem?