Dear Forum



I am trying to concatenate some text values with semi colons in between, ignoring cells with no entry

I used with initial great success the string concat function by Pearson, by placing code in to my sheet and using the =StringConcat function

BUT, All worked well but then it seemed to stop working and my formula gave #NAME? errors everywhere I used it.
Its almost like something just stopped working

Any ideas why? is it some setting on my Excel?

Should I go about the concentration differently?

Appreciate any help at all

Thank you

The Name? error usually means that your formula is looking for a named range rather than a reference. Double check your formula to ensure quotes are in the right place. I would use this formula =if(a3="","",a1&";"&a2&";"&a3) etc.

If this dosnt solve the issue, go ahead and post your workbook.

Thank you.

I have attached a stripped down version of the file with formula and code

I got the code from the following site - which was really helpful. But I can't seem to get the formula to work anymore. It seemed to stop - perhaps I am using the wrong file type - or I inadvertently changed something important

hhttp://www.cpearson.com/excel/stringconcatenation.aspx

Again, appreciate any help

thanks.

Attached Files

Data.xlsm (59.7 KB, 12 views)

Thanks for posting this. All I did was take your code and place it in a module rather than in the worksheet code. This is exactly how Microsoft suggests to write functions. After I did that it worked no problem. The only thing I can think of as to why it worked before could be your worksheets weren't arranged the way they are now. Don't worry too much about as to why since putting it in it's own module works. Give that a shot and let us know.

Did you get a chance to put it in its own module and try it? Let us know if you got it to work. If not we are still here to help.

Thank you very much for your help a few days ago now. it was much appreciated

You are very welcome. hopefully you got it working ok.