VBA Code to Extract Text Between Delimiter/Characters

[jadown]

I have a column that contains names along with other characters in the cells. I would like help with a macro to extract the name from this column. I would like the code to go through each cell in column A and extract the name. Below is an example the spread sheet and what I want to see.

Please Login or Register  to view this content.

I would like the extract to be in the format of last name first comma then first name. But if I could just extract the name like above I'd be happy with that.

Thanks!

[dflak]

You don't need vba to do this. "Straight" excel can handle it. Look up the Find formula. You can start with using some helper cells and then if you really want to substitute the commands back until you define everything in terms of the column containing the name.

However, you are going to have to define the "rules" - For example, if the line contains a "@", rule 1 applies: Look for the second slash and name starts there. Then look for @, name ends there. Substitute blank space for period.
If the line contains "-", rule 2 applies: Look for the period, then look for the dash. Name starts there and goes to end of line
If the line contains ",", rule 3 applies: Look for the comma - this is where the last name ends. Look for the pipe symbol. This is where the first name ends.

Confirm that these are the rules. Then you can use find, left, right, mid and trim to parse out the pieces. I would advise against substituting the pieces back to make one master formula. It would most likely be so complicated that nobody would be able to understand it. Leave the helper columns in place.