Text to Column in cell with mixed cases

[mitsoulas13]

Hi guys,

I have a column filled with cells as the example below:

COMPANY NAME (in Upper case,not fixed number of words) Category Name (in proper case, not fixed number of words)

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 401"]
<tbody>[TR]
[TD="width: 401, align: left"]BRAVO AQUA PARK Playgrounds

[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BRAVO TRAVEL Travel Agencies - Ticket Agencies [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ECONOMIC GROWTH AE Business Consultant

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 401"]
<tbody>[TR]
[TD="width: 401, align: left"][/TD]
[/TR]
</tbody>[/TABLE]
and so one (total number of cells 476K). The only way to distinguish[FONT=arial, sans-serif] the company name and the category name is that the company name is always in upper case and the category name starts in proper case)
[/FONT]
I want to text to column those cell, splitting them by the case, meaning:
[TABLE="width: 500"]
<tbody>[TR]
[TD]BRAVO AQUA PARK[/TD]
[TD]Playgrounds [/TD]
[/TR]
[TR]
[TD]BRAVO TRAVEL[/TD]
[TD]Travel Agencies - Ticket Agencies [/TD]
[/TR]
[TR]
[TD]ECONOMIC GROWTH AE[/TD]
[TD]Business Consultant[/TD]
[/TR]
</tbody>[/TABLE]

Does anyone have any clue how I'm supposed to do that (if that's possible)? Thanks in advance

 

[AlKey]

Maybe this
Enter array formula in B1 and copy down

=LEFT(A1,MIN(IFERROR(FIND(CHAR(ROW(INDIRECT("97:122"))),A1),""))-3)
***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER

then in C1 and copy down

=TRIM(SUBSTITUTE(A1,B1,""))

Book1
	A	B	C
1	BRAVO AQUA PARK Playgrounds	BRAVO AQUA PARK	Playgrounds
2	BRAVO TRAVEL Travel Agencies - Ticket Agencies	BRAVO TRAVEL	Travel Agencies - Ticket Agencies
3	ECONOMIC GROWTH AE Business Consultant	ECONOMIC GROWTH AE	Business Consultant
Sheet2

 

[Special-K99]

Try this

=REPLACE(A1,MIN(FIND(CHAR(ROW(INDIRECT("97:122"))),A1&"abcdefghijklmnopqrstuvwxyz"))-2,1,"@")
Array formula, use Ctrl-Shift-Enter.

This finds the first lowercase letter in the string, goes two positions back (should be a space) and changes it to an 'at' sign.
Then use Text To Columns with a delimiter of @ to separate the com[pany name from the description.

Source based on this: How to find position of capital letter in a text string?
Post #7

 

[mitsoulas13]

Thank you both for your replies.

Both solutions work like a charm but I'm having another problem now. See, some of those cells (I didn't included any of these at the example) have Greek letters. Meaning the "97:122" Ascii codes don't work at those cells.

Any idea how to work this out?

Again, many thanks for your replies!

Example Table:

[TABLE="width: 274"]
<tbody>[TR]
[TD="width: 274"]TALLY WEIJL - ΘΗΡΑ Μπουτίκ
[TABLE="width: 274"]
<tbody>[TR]
[TD="width: 274"]ΑΛΕΞΙΟΥ Α ΕΠΕ Φυτώριο
[TABLE="width: 274"]
<tbody>[TR]
[TD="width: 274"]ΔΗΜΟΣ ΓΕΩΡΓΙΟΣ Υδραυλικά είδη [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]