Macro to find and store the last words in a text string

[rockja]

Hi, I am formatting an exported Excel report from another program using a macro. I need to extract the project name which is at the end of a text string. It needs to be stored as a defined variable to be used and called upon later.

For instance:

Project Name of cell A1 would be = ProjName

The text string is always stored in cell A1 and looks like this:

"Current Solution Summary Report Project: Project Name"; where 'Project Name' will always be different. The the first five words "Current Solution Summary Report Project:" will always be the same.

Thanks!

 

[Blade Hunter]

If you guys keep shortening it at this rate pretty soon it will be as simple as:

1=1

 

[ajlowndes]

you're right, nice