Macro to format exported names

[slick9456]

I have a Macro that I pulled through various sources and am stuck at the final piece of the code to get it it do what I want. I was hoping a second pair of eyes can help me here. Originally I tried recording the text to column application and modifying that however, that turned out to be more complicated.

The goal of the Macro is to Format both columns Q & T so they simply read "FirstName LastName". The formula for Column Q works, however I found that it will not work for Column T because their is no comma(,) in the formatting of this column of Data.

Since this data is coming via export, I have no control over the format. The Macro is attached to the sample data on the spreadsheet below and you will see what I mean when you run it initially. Any thoughts on how I can get this to work?

Also ideally having the column references to go by NAME vs Column letter would be great, but if too great, it would still be a huge win to get the formatting of Column T working.


SAMPLE DATA w Macro Sample Data 4.xlsm

Desired DATA OUTPUT Sample Data 4 OUTPUT.xlsm

[patel45]

I did not understand, can you attach a file with desired output ?

[HaHoBe]

Hi, slick9456,

maybe replace

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

Ciao,
Holger

[slick9456]

Hi HaHoBe, I tried replacing the code with what you advised, the output data in that column comes out as:

"LastName First Name M." whereas I still need the data in Column T to mirror the format in Column Q as "FirstName LastName"

[slick9456]

I did not understand, can you attach a file with desired output ?

I have attached a Sample Output sheet as requested.

[HaHoBe]

Hi, slick9456,

did you try out the suggestion I supplied in post #3 with replacing the formula to use for Column V?

Ciao,
Holger

[patel45]

did you try HaHobe solution ?

[slick9456]

Hi, slick9456,

did you try out the suggestion I supplied in post #3 with replacing the formula to use for Column V?

Ciao,
Holger

EDIT: I did, it comes out in the format "LastName FirstName M." which is different than how Column Q is output (my goal) "FirstName LastName"

[slick9456]

Hi, slick9456,

did you try out the suggestion I supplied in post #3 with replacing the formula to use for Column V?

Ciao,
Holger

I did try your solution, here's an attached output after applying your suggestion. It seems its off just a little, I would still want the data in Column T(Formula V) to match how column Q comes out in the format "FirstName LastName

HAHOBE OUTPUT ->HaHoBe OUTPUT.xlsm

[HaHoBe]

Hi, slick9456,

as you are posting in the VBA forum I wonder why you would need an extra long Formula instead of using some VBA code and a slightly simpler formula to get the very same result.

Replace

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

and you could omitt the replacement for that column thereafter as well.

Ciao,
Holger

[slick9456]

Hi Holger,

I wasn't sure where to post the since the issue stems from the macro. Per the formula you provided it seems that this line is referencing the specific ID I placed in the sample data:

Please Login or Register  to view this content.

However this won't work in the broader sense because you are referencing a specific instance of an ID (00900123123) which will be unique for each employee in my company, so this number can be completely different per each row of data. I also wanted to remove the Middle Initial and period from their names in the process so that it truly only reads as First Last and nothing else.

Which is why the first function works so well because it will work for any variation of data, as long as it maintains the same format. However I just need to modify that to since I found out that the format of the data in Column T is different than from Column Q in that the data in Column T does not have a (,) which is partially what the original formula searches for to work.

I am open to any simpler variation of a formula to work, but the goal would be to have it be able format the data in that column regardless of name length or ID # to get it in the format First Last.

Sorry for the confusion.

[TMS]

Not sure if this is resolved, but I think this will do the job:

Please Login or Register  to view this content.

Regards, TMS

[slick9456]

Hi Trevor,

I just tested this on my sample data and I think this is what I am looking for. I will test with some real data shortly and post my results.


Thanks!

[jindon]

Try this too,

Please Login or Register  to view this content.

[slick9456]

Wow, this works as well jindon. Just curious how this varies from the code TMS provided as from looking at yours, it seems more compact and as column positions may change in the future, this seems easier to update the column references for, in the event of any shift.

Try this too,

Please Login or Register  to view this content.

[TMS]

It is indeed much more compact. Don't you just love Regular Expressions!? I think I'm going to have to spend some time playing with them as I really struggle to get my head round it. Effectively, it still has to process the same number of cells but I suspect it will be quicker than mine.

If the columns are likely to change and/or there might be more of them, I'd change my code to use column references and loop through the columns.

With stuff like this, particularly for clients, I'd use a Configuration sheet which could be used to list the columns to be processed (either in separate cells or as a comma separated string in one cell).

Regards, TMS

[jindon]

Used Regular Expressions which allows you to recall and manipulate matching substring of pattern matching.

In this case, you want "First Last" from
"Last, First M. (00900123123)"
or
"Last First M. (00900123123)"

Logical matching is
1) Block of characters from the beginning that excludes comma and space.
gives you "Last"

2) Block of characters after 1) that excludes sapce.
gives you "First"

Then swap the order of 1) and 2)

That's what the code is doing.

[TMS]

@jindon:

That's what the code is doing.

Easy for you to say.

Please Login or Register  to view this content.

just looks like a censored profanity (OK, maybe it is )


Regards, TMS

[TMS]

Improved version; more easily tailored and less repetitive.

Please Login or Register  to view this content.

Regards, TMS