Can excel recognize and split cells, who contain both cyrillic and english text

Try this

Enter array formula in B1 and copy down
**Must be entered with Ctrl+Shift+Enter key combination.

Formula:

Please Login or Register  to view this content.

then in C1

Formula:

Please Login or Register  to view this content.

This is not bulletproof:

	A	B	C	D
1	implied assent подразбиращо се съгласие	implied assent	подразбиращо се съгласие	B1 and down: {=LEFT(A1, MATCH(63, CODE(MID(A1, ROW(INDIRECT("1:100")), 1)), 0) - 2)}
2	mutual assent взаимно съгласие / споразумение	mutual assent	взаимно съгласие / споразумение	C1 and down: {=MID(A1, MATCH(63, CODE(MID(A1, ROW(INDIRECT("1:100")), 1)), 0), 100)}
3	assent procedure процедура на одобрение	assent procedure	процедура на одобрение
4	qualified assent съгласие с уговорка	qualified assent	съгласие с уговорка

Both formulas have to be confirmed with Ctrl+Shift+Enter, not just Enter. There was no need to remove the spaces.

Attached .

Dunno. Maybe another member can open it and see if they have the same problem.

I opened the attachment in post #7 without a problem and formulas worked as advertised.

Works perfectly in Ireland...

Yor profile saya excel 2003, but you attacahed a 2007+ .xlsx. Which are you using?

No. ALKEY's would NOT have worked in 2003 (IFERROR) but there's no obvious reason why it shouldn't work in 2016. Do you know how to use Formulas/evaluate formulae?? Go through it and post a screenshot of the FIRST dialogue box where you see #N/A... and maybe the next one, too (for luck!!).

I should have added - use the file that shg posted, as I also can see that one!!

Can you also post a bit of your own file after applying Alkey's formula?

I was able to improve my formula for better accuracy.
Please see attached file

When I open Book1, I see #N/A UNTIL I enable editing and then it looks FINE!!!

There are no formulae in the other file.

Alkey's file looks fine to me (only a quick glance as it's time to eat!!).

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.