How to move capital letters in separate columns

[excelhero]

Every cell i have capital letters in the end, is it possible to move them to another column
Sample, like you see some times there is some lonely random letter or number and after that comes the part which i want to move another column.

Juvan teollisuuskatu 15 B ESPOO

Ollaksentie 9 A ESPOO

Otsonkallio 2 ESPOO

Rentukkatie 4 B 1 VANTAA

Huopalahdentie 7 HELSINKI

Tuulikintie 16 VANTAA

Eilantie 37 VANTAA

Mittalinja 5 VANTAA

Puolarmetsänkuja 6 B ESPOO

Elannontie 5 VANTAA

<tbody>
</tbody>

Here sample how I want this

Juvan teollisuuskatu 15 B	ESPOO
Ollaksentie 9 A	ESPOO
Otsonkallio 2	ESPOO
Rentukkatie 4 B 1	VANTAA
Huopalahdentie 7	HELSINKI
Tuulikintie 16	VANTAA
Eilantie 37	VANTAA
Mittalinja 5	VANTAA
Puolarmetsänkuja 6 B	ESPOO
Elannontie 5	VANTAA

<tbody>
</tbody>

Help appreciated
BR. Petter

 

[FormR]

Welcome to the forum.

Here is one option that uses formula:

Excel Workbook
	A	B	C
1	Juvan teollisuuskatu 15 B ESPOO	Juvan teollisuuskatu 15 B	ESPOO
2	Ollaksentie 9 A ESPOO	Ollaksentie 9 A	ESPOO
3	Otsonkallio 2 ESPOO	Otsonkallio 2	ESPOO
4	Rentukkatie 4 B 1 VANTAA	Rentukkatie 4 B 1	VANTAA
5	Huopalahdentie 7 HELSINKI	Huopalahdentie 7	HELSINKI
6	Tuulikintie 16 VANTAA	Tuulikintie 16	VANTAA
7	Eilantie 37 VANTAA	Eilantie 37	VANTAA
8	Mittalinja 5 VANTAA	Mittalinja 5	VANTAA
9	Puolarmetsnkuja 6 B ESPOO	Puolarmetsnkuja 6 B	ESPOO
10	Elannontie 5 VANTAA	Elannontie 5	VANTAA
Sheet2

 

[Special-K99]

Try

=RIGHT(A1,LEN(A1)-FIND("@",SUBSTITUTE(A1," ","@",(LEN(A1)-LEN(SUBSTITUTE(A1," ","")))/LEN(" "))))

 

[XOR LX]

Hi.

The desired extraction for the town/city in your examples is always a single word.

Can we presume then that there are no cases where this part consists of more than a single word?

Regards

 

[excelhero]

Hello,
FormR this code left some letters for capitals.

I tried Special-K99 code and it worked !!
Is it now possible to reverse this and delete these "capital letter" towns from A1 column. ?

Thank you so much !!!
saved tons of time =)

 

[FormR]

FormR this code left some letters for capitals.

I would have to see some examples, but if your happy with the solution from Special-K99 you could just stick with that.

Is it now possible to reverse this and delete these "capital letter" towns from A1 column. ?

You could still try the example I gave in cell B1 of post #2.

 

[excelhero]

This post #2 trick worked, thanks FromR
I tried again your c1 example, it worked now.. I made some mistake yesterday when i was trying it, my bad
Amazing how good you are!
Thank you all for help!