split a string from a particular string

[anilg0001]

my A range have the following values
Mr. Gayle AdamsProduction Manager
Mr. Rich ClaussenExecutive Vice President, Talent Development
Ms. Sandra CrannyExecutive Vice President, Communication Planning
Mr. Bryan CrannyDirector, Information Services
Mr. Sean FadenVice President & Creative Director
Mr. Andy FletcherChief Executive Officer
Ms. Karen HenricksenDirector, Human Resources

the name contain there designation also
i need to split there designation to B range
result like

A1 Mr. Gayle Adams
B1 Production Manager
A2 Mr. Rich Claussen
B2 Executive Vice President, Talent Development
A3 Ms. Sandra Cranny
B3 Executive Vice President, Communication Planning
A4 Mr. Sean Faden
B4 Vice President & Creative Director
A5 Mr. Andy Fletcher
B5 Chief Executive Officer
A6 Ms. Karen Henrickse
B6 Director, Human Resources


Designations start like
Production*
Vice President*
Chief*
Executive*
Director*

is it possible to create a macro?

[jindon]

Try

Please Login or Register  to view this content.

[anilg0001]

Thanks mr.jindon
it shows error

Compile error:
Wrong number of arguments or invalid property assignment

Please Login or Register  to view this content.

[jindon]

Working...

[anilg0001]

Thanks it working now
but may i know how code understand the designations?

[jindon]

From the pattern specified.

[anilg0001]

i dont understand

may i know the parttern here?

Mr. Gayle AdamsProduction Manager

[jindon]

I don't know how deep you know about Regular Expressions.

If you don't know, google the net and get the information about regular expressions first and ask where you don't understand.

I don't want to explain from the scratch.

[anilg0001]

ok ok
thanks a lot