Formula (TEXTSPLIT) is applied to single cell / area

**Julia N.** · 01-08-2025, 05:03 AM

Hello everyone,

I hope someone can help me with the follwing problem.
I have a large excel sheet and would like to apply the follwing command to the whole datatable:

Please Login or Register  to view this content.

But it is always applied to a marked cell or area which causes the following error:

"There is a problem with your formula.
Not trying to type a formula?
When the first character is an equal (=) or a minus (-) sign, Excel thinks it is a formula.
- you type =1+1, cell shows 2
To get around this, type an apostrope (') first
- you type '=1+1, cell shows =1+1 "

I guess that I have to mark no cell or area where the formula is applied. But I do not know how this could work.

Can someone help me in avoiding this problem?

Kindest regards and thank you so much for helping me,
Julia

**josephteh** · 01-08-2025, 05:11 AM

Welcome to the forum, Julia! Please see the yellow banner at the top on how to attach a sample workbook.

**TMS** · 01-08-2025, 05:54 AM

If i paste that formula into an empty sheet it works. However, i have no data and no expected results so i don't know if the output is correct.

That said, I suspect you just need to change all the commas ( , ) to semicolons ( ; ) in the formula as i would expect your Regional Settings will use the semicolon as a separator.

**AliGW** · 01-08-2025, 06:18 AM

Where did you find the formula? Was there a sample workbook attached? If so, open it and copy the formula from inside the workbook - it will be in the correct format for your German locale.

**Julia N.** · 01-08-2025, 06:25 AM

Thank you a lot for your answers. They are very helpful for me.

The formulas are from the Internet (US or English) so I guess you are right about the language settings and changed the "," to ";" and the "ß" as separator to "$". Unfortunately something still seems to be wrong because I get the error #NAME?

**TMS** · 01-08-2025, 07:15 AM

You need Excel 365 or Excel 2024 to use TEXTSPLIT. What version of Excel are you using?

**Julia N.** · 01-08-2025, 08:17 AM

I use Microsoft 365. So this should work, hopefully.

**AliGW** · 01-08-2025, 08:28 AM

Please update your forum profile to include 365.

You will need to find out the German versions of the functions. You could try this: https://support.microsoft.com/en-gb/...6-32cc8d326889

Some are here, but not the newer ones: https://www.perfectxl.com/academy/fu...nglish-german/

**AliGW** · 01-08-2025, 08:31 AM

This might get you partway there:

=TEXTSPLIT(TEXTVERKETTEN("ß";FALSCH;MTRANS(MAP(B2:D241;LAMBDA(x; TEXTVERKETTEN("|";FALSCH;x;INDEX(A1:DQ1;0;SPALTE(x));INDEX(A1:A241;ZEILE(x);0))))));"|";"ß";FALSCH)