Why you cannot use some Excel formulas

If I try to use Write Cell activity to write something to Excel, some formulas are working correctly but not all.

E.g. “=TRIM(” + value + “)” works correctly but I would need to use TEXT and CONCATENATE but I did not manage to get those working.

“=TEXT(”“21424"”; ““0000000"”)”
“=CONCATENATE(”“bat”“; ““man””)”

Any ideas how to get those working?

If the problem is you need " in the formula try using chr(34) instead

“=TEXT(" + chr(34) + “21424” + chr(34) + "; " + chr(34) + “0000000” + chr(34) + ")”

1 Like

I still receive following error; “Write Cell : The range does not exist”
Although same Activity works e.g. for TRIM that I mentioned in first comment.

For embedded quotes you can double up with 4 quotes so entire line ends up being one string, like:
“=TEXT(”“”“21424"”“”; “”““0000000"””")”

For “The range does not exist”, you might have a wrong Sheet name or the range field is wrong. So I would check that or you can provide that info here as well.

Problem was that same Activity works if I change e.g. TRIM to formula. I do not touch to sheet name or cell definition.

Do you have a screenie of the activity failing?
You can also place the formula into a message box to verify it shows how you want it to in the cell.

Thanks.

This is now working as it should;
“=CONCATENATE(”“bat”“, ““man””)”

So it works with “,” but not with “;”. In Excel there works only “;” and for some reason some part is converting that “,” to “;”.

2 Likes

Thanks @Mikko for this update! I spent a couple of hours struggling with this until I saw your solution.

Excel somehow accepts only a “,” as a separator. Here in Germany (and in some other countries), Excel uses “;” as a separator in formulae. Writing the “;” in the formula throws the range not found error, whereas using a “,” instead resolves it automatically.