Get uppercase lastname from string

[fred78350]

Hi list,

I have a column containing full names (Firstname LASTNAME) and I want to split this information into two adjacent columns, first one containing only LASTNAME and other one containing LASTNAME Firstname (swapped), using only XL macros (no VBA).

I could of course isolate Firstname from LASTNAME by looking for the space character, but sometimes full names are composed of multiple firstnames and/or lastnames, like in the following example:

Column A
Rich ABBOT
Victor James DELA CRUZ
Fred DRUM GERA
Jo Wilfried ROBERTS

So in column B, I should only have:
ABBOT
DELA CRUZ
DRUM GERA
ROBERTS

And in column C:
ABBOT Rich
DELA CRUZ Victor James
DRUM GERA Fred
ROBERTS Jo Wilfried

I am thinking about checking the first occurrence of two capital letters in a string to detect the beginning of lastname, but I am not such a specialist in Excel macros to know how to do it.

Thanks a lot for your help!

Fred

 

[jasonb75]

Welcome to the board!

I'm guessing what you want is a formula not a macro.

Try

Excel Workbook
	A	B	C
1	Rich ABBOT	ABBOT	ABBOT Rich
2	Victor James DELA CRUZ	DELA CRUZ	DELA CRUZ Victor James
3	Fred DRUM GERA	DRUM GERA	DRUM GERA Fred
4	Jo Wilfried ROBERTS	ROBERTS	ROBERTS Jo Wilfried
Sheet1

 

[fred78350]

Many thanks for your prompt response jasonb75!
It works like a charm, you're a true XL pro

Fred