Macro to Parse String

[Anthony123]

Hello, I REALLY need help soon! I have an Inventory Excel Spredsheet where i need to parse the data in a column. I need to create a Macro to do this. I have to parse this data into 'First Name' , and 'Last Name' (I ONLY NEED THE NAMES, all other data is irrelevant) and does not have to be parsed but my data does not only contain names so i just can't just parse where there is a space or colon. If there is a "Jr' or 'II' or any other name after the last name, it should be grouped with the 'Last Name', all other data (room numbers) after the last name can be in a seperate column. My data looks similar to this:

Column A
ND
ND
Assigned: Jim Scott Jr - Rm 279
Assigned: Joe M Smith - Rm 990
ND
ND
Stored: Room 432
Extra: Room 123
Assigned: Tony Tone II - Rm 356
Assigned: Susie Q Young -Rm 394
Stored: Room 563
Stored: Room 789
Extra: Room 654
ND
Assigned: Jimmy Joe Jr - Rm 276


Please let me know if you need any questions answered! I am on a tight deadline so any start will help! Thanks so much in adavnce!

[Cofad]

This is not a macro, but it can be used to get the names pretty quickly.

Use the text to columns feature to split col a into separate columns and then sort the data to remove spaces and other names like "room".

Do you need a macro to automate the process or is this a one time thing?

[Anthony123]

Thank you. I tried using the text to column freature but didnt know how to only parse the data with "Assigned:" as the first word. I needed a macro because i will be doing this action several times with several different spreadsheets.

[Cofad]

It won't only do the "Assigned" values, but after you sort it you can just delete all the others because they'll be grouped together.

[jaslake]

Hi Anthony

Welcome to the Forum!

This code is in the attached...see if it does as you require. Let me know of issues.

Please Login or Register  to view this content.

[Alf]

Hi John

One of the values given by OP was

Assigned: Susie Q Young -Rm 394

so I would suggest you changed the line

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Alf

[jaslake]

@Alf

Again...a different reading of the requirement. If the OP also wants the Room assignment, as you suggested, easy fix. Thanks.

[Anthony123]

Thanks for all your help! I really appreciate it!

[jaslake]

Hi Anthony

You're welcome...glad I could help. Thanks for the Rep.