Concatenate - Macro way with checking of null cells

**Odeen** · 01-07-2013, 11:57 AM

Hi,

I have data in cells A, B, C, D and want to have results in E.

The thing is, that i want to =CONCATENATE("text|";D2;"|";C2;"|";B2;"|";A2).

If for example in C2 will be empty cell, i need this function not to give there two || separators, but just one.

Idealy, please show me this function in cycle, which will go until empty cell Ax

Thank you very much

**MarvinP** · 01-07-2013, 12:11 PM

Hi Odeen,

Try this formula to do what you want.

=SUBSTITUTE(CONCATENATE("text|",D2,"|",C2,"|",B2,"|",A2),"||","|")

**Odeen** · 01-07-2013, 12:22 PM

Hmm,

not functional. With this function, i can still see || besides if there are two blank columns besides.

And the other thing is, that i would like this function to see made by macro. I have not to border myself everytime i want concatenate some things. In my work very common function.

Thank you very much

**aelgadi** · 01-07-2013, 12:24 PM

Tri this

Please Login or Register  to view this content.

**MarvinP** · 01-07-2013, 12:39 PM

For two blanks together try this formula,

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CONCATENATE("text|",D2,"|",C2,"|",B2,"|",A2),"||","|"),"||","|"),"||","|")

If you need more then supply a sample workbook and let's see the real problem. Is it always columns D,C,B,A or is it the 4 columns to the left of the Concatenate formula?

**Odeen** · 01-07-2013, 03:40 PM

aelgadi

-

Hi, somehow after running this script of yours, nothing writes down in cells E at all column.

What is wrong? I have Czech version of office, so i changed sheet to name of list, but nothing else were wrong in script (by debugger)

Thanks for your assistance.

**Odeen** · 01-07-2013, 03:41 PM

MarvinP

-

SUBSTITUTE WORKS GREAT...
and
YES, it is always 4 columns to the left of E column. So your function is right.

Thanks, new function i know...

now just the macro way... it is for this time more important to me

**aelgadi** · 01-07-2013, 04:00 PM

Is there any empty cell in Column A between Occupied cells or after the header?
If the answer is "No", the only thing I can think of is the fact the macro is manupilating the wrong sheet
so instead of sheet1 try to use Worksheets("Exactly type the name of sheet") as follows

Please Login or Register  to view this content.

Note: The sheet name appears on sheet tab

**Odeen** · 01-07-2013, 04:24 PM

aelgadi

-

wow - this works great. So my mistake... but i have found one mistake.

if i have columns like bellow, the script somehow did not find out, that there is new line, and concatenates also previous line.

Please correct it for me. Anyway - thanks, you are great man

A B C D E
ab1 be1 ce1 de1 ab1|be1|ce1|de1
ab1 be1 ab1|be1|ce1|de1|ab1|be1

**aelgadi** · 01-07-2013, 04:52 PM

If I'm understanding you correctly try the following

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Note: You need to run the macro again every time you introduce a new row

**MarvinP** · 01-07-2013, 04:55 PM

Ok Odeen,

Here you go. This is much more than you wanted but attached is a Function that works like this. You can put in any range of cells in the FcnOdeen("range") and it will concatenate the cells from the last to the first and return what I think you want.