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

Unit 12, Wellington Fair 40 Lord Street (Entrance off Wellington Street) EAST PERTH
Building 3, Level B, Suites B53-B56 1 Kirinari Street BRUCE
Level 3 106 Edward Street BRISBANE
Post Split
Unit 12, Wellington Fair 40 Lord Street (Entrance off Wellington Street)	EAST PERTH
Building 3, Level B, Suites B53-B56 1 Kirinari Street	BRUCE

<tbody>
</tbody>

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.