Macro to split a string into 2 texts based on where is the capital letter

Hi guys,

I have an odd export and i have normal names and combined names. When i say normal I say e.g Ionut etc.
The odd format comes from the export and it seems that some names have the format like JohnMonet.

I would like to ask for help from you guys, in order to run a macro that will split these formats into John and Monet. I can insert a new column --E and in that E i want to have the Monet and the rest to stay in col. D.

The macro should run only when it will see that inside of a word is another capital letter and will take the rest of the word starting from the capital letter to the rest of the word and will paste it in D.

Thank you,
Ionut

Can you give some more examples please? I sort of understand but "inside of a word is another capital letter and will take the rest of the word starting from the capital letter to the rest of the word" has thrown me a bit, are you saying that "sauSages" would be split as sau & Sages?

By formula maybe??

Formula:

Please Login or Register  to view this content.

Here you go pjwhitfield

Thank you Glen. This is what I need, however I want it in a macro because i want it to test if there is a capital letter in the word or not. Because I have in col. D and correct names and if i drag and drop the formula into correct cells the result is different.

Thank you for your reply!

Thank you Jindon!

I have an error "Run time error 5" Invalid procedure or call or argument.

I have to install something as Reference in the Library?

the highlighted line is ==> a(i, 2) = .Execute(a(i, 1))(0)

Yeah Thank you Jindon! It is what I needed. The error comes from a name BRETT which has all the letters as capital letters.

However I made them correct and it worked. Is there a way to put this code to see if all the letters are capital letters to skip the process of splitting?

Thank you for your help!

Jindon you rock!

Thank you very much for your help!