Replace cell contents with portion of cell contents

[tecsbrain]

Hopefully I'm not a moocher, but for my second request this week, similar to my previous thread (https://www.excelforum.com/excel-pro...ml#post5194089), I use a software package that spits out some data that I need in a different format. Rick Rothstein kindly provided a small macro to reformat some text strings into a proper, usable time format. I have been poking at that code trying to understand it and repurpose it into this problem, but I'm stuck because ... well I believe his code changes some characters around then defines what format the numbers should be in. This works perfectly for numbers that have defined formats, but not text strings like what I'm working with.

Basically, I am given employee names in a format "nnnn - Lastname, Firstname" (with nnnn being a badge #), but I need it in FirstInitial. Lastname format. I have been using a formula to do this:

Please Login or Register  to view this content.

This formula works for hypenated last names as well as multiple names as one would find in a Hispanic country (Jorge Francisco Lopez Gonzales, for example, where there is no clear middle name but multiple words in a name, shown as "Firstinitial. Name1 Name2 Name3".

Thanks in advance for any advice.

[alansidman]

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[tecsbrain]

I've gone ahead and posted the workbook that I keep just so I can copy the formula ... Column F represents what I'm starting with, Column G represents the result I'm looking for ... but I would like the macro to replace F with the format of G without having to insert columns.

[huuthang_bd]

You can try below code. Select range containing the names which you want to convert into the format as you like and then run the macro.

Please Login or Register  to view this content.

[ikboy]

UDF:

Please Login or Register  to view this content.

[jindon]

Simply

Please Login or Register  to view this content.

[alansidman]

An alternative solution is to use Power Query. No coding required. Any names added to your list will automatically be reformatted when the refresh button is selected. Here is the Mcode and a file attached for your review.

Please Login or Register  to view this content.