# [Solved] Formulas: If or Concatenate???

• I need help joining several cells together and adding dashes inbetween each cells information.

The problem is that some cells have no information. What I am ending up with right now is this:

scattered throughout/ALL flew all directions/ / / /

because the last four cells have no information.

So I am assuming I have to determine in my formula if the cell is blank. I was thinking of this:

=IF(K5="",L5, . . . . . I'm not sure what!!!

Anyway, your help is GREATLY appreciated.

Thank you,
Judie

• For text in cells A1:D1 (as an example) the following formulation works.

Code
``=CONCATENATE(IF(LEN(A1&gt;0),A1,""),IF(LEN(B1&gt;0),"/"&B1,""),IF(LEN(C1&gt;0),"/"&C1,""),IF(LEN(D1&gt;0),"/"&D1,""))``
• I spoke too soon and didn' check my test closely enough. Try the following instead.

Code
``=CONCATENATE(IF(ISBLANK(A1),"",A1),IF(ISBLANK(B1),"","/"&B1),IF(ISBLANK(C1),"","/"&C1),IF(ISBLANK(D1),"","/"&D1))``

P.S. Note that I'm adding the "/" only at the start of the 2nd and following arguments. This way the CONCATENATE result will not end in a "/"

• There is still something wrong:

Here's the formula I typed based on your suggestion:

=CONCATENATE(IF(ISBLANK(K5),"",K5),IF(ISBLANK(L5),"","/"&L5),IF(ISBLANK(M5),"","/"&M5),IF(ISBLANK(N5),"","/"&N5))

and here is the result:

//scattered throughout/ALL flew all directions

• Curious. Your formula looks correct. Here is a sample of what worked for me.

<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis/colo/popup.js"&gt;</SCRIPT&gt;<CENTER&gt;<TABLE cellSpacing=0 cellPadding=0 align=center&gt;<TBODY&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=#0c266b colSpan=7&gt;<TABLE width="100%" align=center border=0&gt;<TBODY&gt;<TR&gt;<TD align=left&gt;<FONT color=white&gt;Microsoft Excel - CONCAT with blank entries.xls</FONT&gt;</TD&gt;<TD style="FONT-SIZE: 9pt; COLOR: #ffffff; FONT-FAMILY: caption" align=right&gt;___Running: xl97 : OS = Windows NT 4 </FONT&gt;</TD&gt;</TR&gt;</TBODY&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px" bgColor=#d4d0c8 colSpan=7&gt;<TABLE width="100%" align=center border=0 VALIGN="MIDDLE"&gt;<TBODY&gt;<TR&gt;<TD style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: caption"&gt;(<U&gt;F</U&gt;)ile (<U&gt;E</U&gt;)dit (<U&gt;V</U&gt;)iew (<U&gt;I</U&gt;)nsert (<U&gt;O</U&gt;)ptions (<U&gt;T</U&gt;)ools (<U&gt;D</U&gt;)ata (<U&gt;W</U&gt;)indow (<U&gt;H</U&gt;)elp <A onclick=show_popup(); href="#javascript:void(0)"&gt;(<U&gt;A</U&gt;)bout</A&gt;</TD&gt;<TD vAlign=center align=right&gt;<FORM name=formCb755237&gt;<INPUT onclick='window.clipboardData.setData("Text",document.formFb078704.sltNb935705.value);' type=button value="Copy Formula" name=btCb873980&gt;</FORM&gt;</TD&gt;</TR&gt;</TBODY&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=white colSpan=7&gt;<TABLE border=0&gt;<TBODY&gt;<TR&gt;<FORM name=formFb078704&gt;<TD style="WIDTH: 60px" align=middle bgColor=white&gt;<SELECT onchange="document.formFb078704.txbFb426622.value = document.formFb078704.sltNb935705.value" name=sltNb935705&gt;<OPTION value='=CONCATENATE(IF(ISBLANK(A1),"",A1),IF(ISBLANK(B1),"","/"&B1),IF(ISBLANK(C1),"","/"&C1),IF(ISBLANK(D1),"","/"&D1))' selected&gt;F1</OPTION&gt;</SELECT&gt;</TD&gt;<TD align=right width="3%" bgColor=#d4d0c8&gt;<B>=</B&gt;</TD&gt;<TD align=left bgColor=white&gt;<INPUT size=80 value='=CONCATENATE(IF(ISBLANK(A1),"",A1),IF(ISBLANK(B1),"","/"&B1),IF(ISBLANK(C1),"","/"&C1),IF(ISBLANK(D1),"","/"&D1))' name=txbFb426622&gt;</TD&gt;</FORM&gt;</TR&gt;</TBODY&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"&gt;<BR&gt;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle&gt;<CENTER&gt;A</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle&gt;<CENTER&gt;B</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle&gt;<CENTER&gt;C</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle&gt;<CENTER&gt;D</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle&gt;<CENTER&gt;E</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle&gt;<CENTER&gt;F</CENTER&gt;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"&gt;<CENTER&gt;1</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left"&gt;text</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;&nbsp;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left"&gt;tutu</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left"&gt;text3</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;&nbsp;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left"&gt;<A onclick="document.formFb078704.sltNb935705.options[0].selected=true; document.formFb078704.txbFb426622.value = document.formFb078704.sltNb935705.value;" href="#javascript:void(0);"&gt;text/tutu/text3</FONT&gt;</A&gt;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" colSpan=7&gt;<TABLE width="100%" align=left VALIGN="TOP"&gt;<TBODY&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; WIDTH: 120pt; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff" align=left&gt;<U&gt;Sheet1</U&gt;</TD&gt;<TD&gt;&nbsp;</TD&gt;</TR&gt;</TBODY&gt;</TABLE&gt;</TD&gt;</TR&gt;</TBODY&gt;</TABLE&gt;<BR&gt;<FONT color=#339966 size=1&gt;[HtmlMaker 2.41] </FONT&gt;<FONT color=#339966 size=1&gt;To see the formula in the cells just click on the cells hyperlink or click the Name box</FONT&gt;<BR&gt;<FONT color=red size=1&gt;PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</FONT&gt;</CENTER&gt;

• You have a ";" entered before M5 in the 3rd argument of the M column IF statement. Remove it and I think you'll be in business.

• =CONCATENATE(IF(ISBLANK(K5),"",K5),IF(ISBLANK(L5),"","/"&L5),IF(ISBLANK(M5),"","/"&M5),IF(ISBLANK(N5),"","/"&N5))

I recopied my formula from my worksheet, as previous.

There is definitately NOT a ";" in my formula. I don't know how that appeared. But I'm positive I have the same as you have.

J.

• I swear - there was no ";" when i just recopied this

and there it goes again - it appears when I post.

Otherwise, everything is the same.

Any more ideas?

J

• Tom:

I have an idea. Try your formula, when the first two cells are BLANK, and see what you get.

J

• I was making the assumption that the first cell would always have text in it. If not, then the leading "/" appears.

I've got to think a bit more about this. Maybe something can be done with Dynamic Named ranges and Vector formulas.

It certainly can be done in VBA. Is this an option for you?

• VB is ok, but remember I am a newbie with VB - just getting my tootsie's wet.

Judie

• Ok. I think I User Defined Function might serve your purposes best and give you the most flexibility. The following code (place it in any general module) defines a function that I named "Lump" It takes one argument (the range you want to work with). It is entered into a cell simply as =Lump(A1:C1) if you want to work with cells A1:C1.

See the examples below in cells A6, C6 and A9. Note that you can use a rectangular range. lump will work across the top row then the next row etc.

• Tom: I will try this tomorrow and let you know. According to your example, this looks really GOOD.

My question is this: I am confused as to where I enter this. I know in the =formula drop-down bar, there is a user-defined selection. Is that what you mean? Please remember -- I'm still learning.

Thanks again, and I will let you know what happens. Your example is excellant and SO ARE YOU!!!!

Judie

• Hi, Judie,

The code needs to be entered via the VBA Editor. While in Excel hit Alt+F11 and it will open the editor. After you are in the editor, you may need to add a module if one does not already exist. Make sure you add it to the wookbook that you are working in (that is, if you have several wookbooks open, the VBA Editor will show the VBA pieces of all of them. If you have any addins, these will also add to the VBA editor entries.

If there are multiple projects showing, then under the project list select the Project that has the name of the Excel Workbook you are using. Then from the tool bar do a INSERT &gt; MODULE. Double click on the module name (probably Module 1) in the project list and it should open. Simply paste the Lump function code into this module and you should be good to go. Just close the editor and work as you would thereafter in Excel. The Lump function should now be available for your use.

The following links to tips on OzGrid has a few that relate to VBA. There is one on the editor, but it may not be very helpful for getting started. Excel texts (like Walkenback's "Excel xxxx Power Programming in VBA" give good tutorials on the editor. Sitting down with a colleague familiar with VBA is probably the best method if you have one available.

Good luck, and please post back if you need more help.

• Would the below generate the desired result, given A1:D1 as the range of interest?

Code
``=A1&IF(LEN(A1&B1)&gt;LEN(B1),"/","")&B1&IF(LEN(B1&C1)&gt;LEN(C1),"/","")&C1&IF(LEN(C1&D1)&gt;LEN(C1),"/","")&D1``

It will show a semi-colon near the end of the formula; ignore it.

• Assuming you have information in A3,B3,C3,D3 the following formula will work.

This is only a bad alternative to the other excellent replies you have got! :).

Hope this helps,

Thanks,
Rennie

=IF(CELL("Type",A3)="b","",A3&"/")&IF(CELL("Type",B3)="b","",B3&"/")&IF(CELL("Type",C3)="b","",C3&"/")&IF(CELL("Type",D3)="b","",D3)

• Thank you all for your wonderful replies. We had an unexpected emergency yesterday, so I still haven't been able to sit down with these responses and analyze which would work better for my project. But, later today, tonight, and tomorrow I am going to try EACH one.

I will definitely reply and let you know.

Thank you again for everyone's advice.

This is the GREATEST forum. And . . .

YOU ARE ALL SO, SO SMART.

Thank you,
Judie

• Julie,

I've played a bit more and thought the following updated code might be of use to you IF either you might change the field delimiter in the future and want to updat the "Lumped" cells, or if you wanted to use different delimiters for different cells. Placing the delimiter as an argument to the function has the advantage that the cells with the Lump function will update automatically (if calcualtion is in Automatic) if the argument is changed. It will not do so if you change the delimiter or the delimiter reference from within the VBA code.

The revised code takes two arguments: the RANGE to lump (as before) and the delimiter. The delimiter argument can be a cell reference or the delimiter itself (in quotes). Look at cells A8, A10, A12, and A14 for examples. (Formula displayed as text in the cells above them.)

[code]
Function Lump(rng As Range, Sep As String)
Dim n As Integer, CellCount As Integer
Dim text1 As String, text2 As String
CellCount = rng.Count
text1 = ""
text2 = ""
For n = 1 To CellCount
If Len(rng(n)) &gt; 0 Then text1 = rng(n)
If Len(text1) &gt; 0 And Len(text2) &gt; 0 Then text2 = text2 & Sep & text1
If Len(text1) &gt; 0 And Len(text2) = 0 Then text2 = text1
text1 = ""
Next n
Lump = text2
End Function

• Well, I finally got a chance to review and test all of the responses. My form is LOOKING GOOD!!!

Thank you to all you helped: Tom, Rennie, and Just Jon.

You are ALL fabulous. And to Tom:

You are the AMBASSADOR of HELPFULNESS! I cannot believe the amount of time you spent helping me.

Thank you so much.

Judie

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!