Split String at occurrence of Uppercase

[Blessy Clara]

Hi

I have a column of address - with the City in Upper case, I would like to split the string at the occurrence of Uppercase from right

Example - Uppercase i have highlighted as Red here

[TABLE="width: 500"]
<tbody>[TR]
[TD]Unit 12, Wellington Fair 40 Lord Street (Entrance off Wellington Street) EAST PERTH[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Building 3, Level B, Suites B53-B56 1 Kirinari Street BRUCE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Level 3 106 Edward Street BRISBANE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Post Split[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Unit 12, Wellington Fair 40 Lord Street (Entrance off Wellington Street) [/TD]
[TD]EAST PERTH[/TD]
[/TR]
[TR]
[TD]Building 3, Level B, Suites B53-B56 1 Kirinari Street [/TD]
[TD]BRUCE[/TD]
[/TR]
</tbody>[/TABLE]

Thank you

 

[Fennek]

Hello,

try this code

for i = 1 to cells(rows.count,1) 'all cells in column A
    for k = 1 to len(cells(i,1) -2 'loop all characters
        if mid(cells(i,1),k,2) like "[A-ZA-Z]" then cells(i,2) = left(cells(i,1),k)
    next k
next i

(untested)

regards

 

[Blessy Clara]

Hi Fennek,

Thank you for the code - Please i need some guidance to go about , I am getting compile error - Invalid outside procedure?

 

[Fennek]

do you need this?

'NO option explicit
sub iFen()
for i = 1 to cells(rows.count,1) 'all cells in column A
    for k = 1 to len(cells(i,1) -2 'loop all characters
        if mid(cells(i,1),k,2) like "[A-ZA-Z]" then cells(i,2) = left(cells(i,1),k)
    next k
next i
end sub

 

[Blessy Clara]

Hi Fennek,

Thank you for the suggestion, i tried , not getting an error this time but nothing happening, the cols are not split.

 

[István Hirsch]

Try this:

Excel Workbook
	A	B	C
1	Unit 12, Wellington Fair 40 Lord Street (Entrance off Wellington Street) EAST PERTH	Unit 12, Wellington Fair 40 Lord Street (Entrance off Wellington Street)	EAST PERTH
2	Building 3, Level B, Suites B53-B56 1 Kirinari Street BRUCE	Building 3, Level B, Suites B53-B56 1 Kirinari Street	BRUCE
3	Level 3 106 Edward Street BRISBANE	Level 3 106 Edward Street	BRISBANE
Sheet1

 

[Blessy Clara]

Hi István Hirsch

Thank you very much , yes split as required.

 

[István Hirsch]

You are welcome.