converting text (without delimiting) to an excel column

I copy a list of names from a page, then paste these names into Notepad (unicode encoding).
I then (manually) sort thru these names and enter a CR at the end of each name (to put them into a column)
I then copy this column and paste it into an excel column.
This is a VERY labor intensive way of doing this.

s there faster way of doing this with data that has no real delimiter? Perhaps a different way of copying that would add delimiters?
The ONLY way that I can see to separate the names is that (in general) the end of a name is a lower case character and the first character of the next name is a capital with no space or character.
Also, the name may have spaces in it (ie: Jeffery B Smith) and are from 3 to 25(about) characters in length.

Unfortunately, the characters are not all English (some are Chinese, Japanese or even middle eastern) and while I can sort them, I'm not sure I can automate it.
If I have to edit some of them, but MOST names work, that would help as well .
Thanks for any help

Are you saying that it is not possible to do what I was asking?
Not understanding your post
Thanks

It may help if you were to give us a sample of what you start out with plus the resulting Excel file that you have manually created from that same data. That way it makes sure that we understand the problem and also that we have something to test potential solutions against.

Not sure how I can give a sample. Screen shot of original data, then the data in notepad, then the data in excel? I will attempt to attach them to this post.
They are, in order
1-List as it appears in the web page. I highlight them and copy & paste it into Notepad which gives me:
2-An ascii string of characters in on line. All the names, run together. I then find the end of each name and hit enter (CR) which gives me:
3-the list of names in a column. I copy and past this to Excel which gives me this:
4-A column of names in Excel that I can use
Hope this explains it better

I would like to, esentially, automate this task
Thanks

OK thanks, that helps.

It looks like the copy paste from the web site immediately gives you a hard to work with list.

One straw to grasp at: Have you tried Web Query? Data / Get External Data / From Web.
It's meant for getting HTML tables really, but see if it can make sense of your name list. I don't have much experience with it so I don't know, but it's worth a quick try.

Failing that then I think your observation that names are separated by an upper case character immediately following a lower case character might be the way to do it. That will be a bit of VBA - I'll look into that.

If you copy from the web page and paste straight to excel, does it go into a single cell?

If it does then try running this with that single cell selected.

Please Login or Register  to view this content.

It will split the list down into subsequent cells, just make sure you have enough empty cells below to take the list.

You will find that it splits up some names incorrectly, McDonald or MacDonald for example. Might be a way to specifically exclude Mc* and Mac* from the split, but I don't understand the regex patterns well enough to do that. The bit I've done took a fair bit of trial and error to get it working right.

The one failure in the example name list provided in post #5 was:

John Munn IIPatricia Johnson

It's hard to see how to catch this

Jason: My problem I mentioned above in post #8 with running your macro was that I had "Option Explicit" in effect where I put your Sub which resulted in an "n not defined" error. I added "dim n as long" which resulted in a "type mismatch" on the activeCell.resize line. I guess I have the wrong type for n. After deleting my Dim statement and the Option explicit, your sub worked just fine. A tenth the size of mine and ten times more elegant

WOW, you guys really come thru!
These are REGULAR Macros? not event macros (i learned about in the last thread)?
I will play with these. Even if there are a few hicups, paring the list down to just a few that I need to handle manually, it is worth it!
One anomaly that I have noticed in copying from the web page, If the list I copy (same web page) is running under MS Edge then pasting the copied cells into notepad they paste in a column instead of a row (#3 attachment). Under chrome, which is what I use, it works the way I described. I don't know what the difference would be or if it is significant? Thanks for all the help

Yes, the paste goes entirely into the cell chosen.
Looks like this could do it. Let me check it out!
Thanks

OK, I ran jason.b75's code. Seems to run well. Took 2500 names down to about 150 that I have to manually adjust.
along with the above mentioned Names like Mc and Mac, here are a few fun ones
蔡佳興
Marcelo Adolfo H
Niyazi Gündoğdu
Angelo Di Nardo
ChiKin Hui
วันดี ทองสุข
But paring it from around 2500 to 150 is WONDERFUL!
Thanks

Any patterns among the 150 exceptions? For example looking at the handful of exceptions you list above:

- breaking on two consecutive upper case letters would catch "Marcelo Adolfo H" and the following name

- breaking on a change of language specific Unicode characters would catch the Chinese and Thai names

Any change would have to catch a fair percentage of the exceptions to be worthwhile and there would be a need to check that any change doesn't break more than it fixes.

Breaking on on consecutive Upper case as well as a change in language could be useful.
Is there a way to to address the Mc and Mac issue? perhaps an exception for a capital after the small case c?
The only other one that jumps out is a break after a period, as in "Joe Blow Sr."

Is there any way I can plug these in one at a time to make sure they perform properly individually before I add them all together?
There are a few others but I can't see a pattern and they are pretty isolated

I had NO IDEA that there were so many ways of doing some of these things in Excel.
I had always considered myself pretty good at Excel , but you guys just BLOW ME AWAY
Thanks for the help you have given
Joe

Here is one that breaks on the Mc "Bud McSmith" and one for the Mac "Bob MacPhail"
I will look at the source code for the next set i copy and stick it in a text file. If that will not violate any rules

Posting html code isn't against the rules, you just can't post it directly. Sometimes the posted code can conflict with the code that displays the forum pages, then things start getting messy (seen it before on other forums), posting code directly is blocked to prevent that.

Using lower case c followed by an upper case character as an exception for Mac and Mc could cause other issues.

Try this code

Please Login or Register  to view this content.

With this in the cell and you will see what I mean.

Lizette Thiart DucBud McSmithBob MacPhail

I tried various methods with Mc and Mac in the pattern instead of just c, but nothing works.

I ran Jindon's code from post #19 and Jason.b75's code form Post #7
Both did a good job. Jindon's took care of almost all the "Sr." issues but raised a few others.
Jason's code did a good job as well. Same issue's as before.
I will attache the data test file (.txt) as well as the .xlsm file with the delta's colored
Thanks

OK, here is an updated Macro.

It now deals with the following:

1) Name break on consecutive upper case characters
Example: Catches an upper case at the end of a name: Marcelo Adolfo H

2) Name break at a language change.
Example: Adds a name break when transitioning to or from Thai or Chinese characters

3) Name break at a period (.)
Example: John Smith Sr.

4) Make an exception to a name break for consecutive upper case characters for consecutive upper case "I"s
Example: Ben Jones II

5) Make an exception to a name break for an upper case character following a lower case character for "Mc" and "Mac"
Example: John McDonald or Alan MacStruan

Each of the above options can be turned on or off via the set of flags at the very top of the VBA module. The attached worksheet is delivered with all options enabled. To disable an option change the value of the associated flag from TRUE to FALSE.

With everything set to FALSE except the basic "laUa_enabled" flag you should see the same results as the file I delivered under post #8. I believe (without knowing for sure) that this should also behave identically to Jason's version with your 2500 name test. That would be interesting to confirm.

With all options enabled my macro should deal correctly with all of the specific anomalies noted in your attachments to post #5 and post #13 with the sole exception of "ChiKin Hui" which still generates an erroneous name break after "Chi"

To run the macro:

0) Download the updated workbook attached to this post

1) Paste your name list in cell A1

2) Run macro SplitNames_Geoff

3) The separated names will appear in Column-A starting at cell A2

Hopefully this should improve on your 150 exceptions. I'll be interested to know!

I missed all of the page-2 back and forth before I posted #21 - I'll check what I have against your post #20 attachments.

Attached is an update to post #20 comparison spreadsheet. There's some anomalies I will investigate. The one thing I am doing is recognizing the Chinese / Thai names

Originally Posted by Cpt_kludge

Jindon's took care of almost all the "Sr." issues but raised a few others.

Need to see your desired result.

I changed the Macro to yours from post #8
Other than starting the string in A2 it seems to work identically
Here is an excel that I pasted into a new excel file.
I will play with the new file and see how it does.
Thanks

You have run my version from post #8. The version from post #21 has significant improvements. Please try running the psot #21 version!

I think the only two discrepancies I have are:

1. Su OG I am erroneously splitting into Su O and G

2. Bradley F. Washko Sr. I am erroneously splitting into Bradley F. and Washko Sr.

Here is an updated comparison file:

Attached is a minor update that catches the case of a period after a middle initial like: Bradley F. Washko Sr.

I think that leaves just one failure against your post #20 list in that I am splitting Su OG into "Su O" and "G". I don't see how I can correct this without breaking more than I fix.

Jindon, here is the .xlsm with the desired column

For the data uploaded.

Please Login or Register  to view this content.

OK- I checked my post #27 version against the desired column in post #28 above and I have just the one discrepancy: Su OG, which I will pass on fixing

Jindon, I ran your code in post #29. using the same data as GeoffW283 RevB file.
Different errors mostly issues with "ń ", "ü", "á", and the like, but a few others as well.
I've attached the file

Need to see a source TXT file.

Jindon, here it is

Try

Please Login or Register  to view this content.

Thanks Jindon.
the code in #35 Worked pretty good only 38 names that needed to be manually tweeked
I wave attached the excel file I put the code into: with the ones I had to tweek highlited
So far MUCH better than I could have hoped

This is not perfect as you want.
Like
"JeanPiciu EmanuelDeAnna DeeBaby Haynes"
Can not be separated correctly since it is out of logic.

Please Login or Register  to view this content.

Here is my final attempt. The number of failures against the 2412 names in 101.txt from post #34 has been reduced from 30 something to 10, primarily by:
a) recognizing "Mac"-like prefixes like De, Van, La, Di, etc.
b) assuming (Chinese apart) that there is always at least a first name and last name separated by a " " and never breaking for any reason on the first name.

In addition I have built in an explicit name exception list for the residual 10 problem names so that they can be properly handled. With this change the macro in the attached workbook now, I think, correctly handles all 2412 names in 101.txt with no errors.

When you grab names from the web page are the names mostly unchanged from the previous time or are the names completely different? If the former then maybe the name exception list is of some value. If the latter then it won't help.

That all said, it has taken me almost 300 lines of VBA code vs Jindon's 27. The primary difference at this point is that Jindon's post #37 code does not catch the "Mac-like" prefixes noted above. I suspect he could add this with a couple of extra lines!

And thanks for the reputation points!

You are more than welcome! The work that you guys put into this blows me away
Even with a few errors, I consider this SOLVED!

Did anyone ever get as far as looking at the source code of the web page?

I don't think that using the excel tools to scrape the data directly from the web will work with this, but given that each name has an individiual border on the screen capture, they must be delimited by something, possibly a simple break tag, in which case it might be possible that the tags could be used to split with 100% accuracy.

I have done similar before for a personal project, but that was around 10 years ago, so things may have changed a lot since then.

Did anyone ever get as far as looking at the source code of the web page?

Great idea. No, the source code was never requested.

I asked for it back in post 16, but didn't push for it once Jindon came into the thread and made more progress with regex patterns.

Ah, so you did. I guess the request fell through the cracks and at this point I believe the OP already thinks we've done more than enough!

I admit, I dropped the ball on the Source code issue
I just ran Geoff's code on 4096 names, had around 30 that I needed to manually adjust, Easy-Peasy
My thanks to all of you for making this task almost TOO easy

I have been using this awesome macro since Jan.
I have come up with a few questions.
There is a part of the macro that seems to allow adding or removing specific names it reads:

'Entry point - split names based on various conditions
Sub SplitNames_Geoff()
'======================================================
'Explicit exception list.
'These names would otherwise not be treated correctly
'This list can be added to as needed
Dim Exceptions() As Variant
Exceptions = Array( _
"Éric Chaput", _
"DeAnna DeeBaby Haynes Miller", _
"Dc Mac", _
"Chonchon Moé", _
"Andrea Fabiana O", _
"Stefanie Weiß", _
"Dirk Jo", _
"Su OG", _
"Stjepan Peji" & ChrW(263), _
"Jason BT" _
)
'=======================================================

I cannot seem to add anything to this. I think I'm following the correct format.
But I seem to NOT be able to add names. I will attach a copy of the original file and the changes that I inserted that did not work!

OK, it's been a while!

Just inspecting your .txt file I think you are trying to add the five names below the last entry that I originally had in the list which was "Jason BT" _

You need to do two things:
1) Add a comma after "Jason BT" so that it becomes "Jason BT", _
2) Remove the superfluous comma after your last name "Dušan Grujič", _ so that it becomes: "Dušan Grujič" _

Give that a try and let me know if that now works. If not I will investigate further.

Got it. It made the Macro run without error, but several of the names did not seem to work.
I reworked it and found that some of the name in the revC version were no longer on my list.
Here is the modified revC and Text file with all the names that currently don't work
Also the macro seem to limit me to 21 names as shown in the current revc xlsm file
Thanks

The "_" character at the end of a line allows what VBA considers to be a single statement to be split into multiple lines for better readability. It looks like VBA has a crazy limit of 21 on how many pieces a statement can be split into. In the attached file I have simply allowed multiple names on a given line, so you can now add 4 or 5 times the current number of names before you hit the problem again (at which time you can make the lines longer still).

I spot checked the "Exclude list" names (both the original set and the new names that you want to add and none of the names I checked seem to be present in cell A1 or in the name list after the macro has been run.

If some of the Exception List names are no longer in your source list then they can, if you wish, be removed from the Exception List, or thay can stay there - that does no harm.

A final note - I have omitted for now the final name in your exception list - "Маришка Тк". The VBA window was having difficult with this character set.

The attached file adds your provided names to the Exception List - that's the only change I made.

I'm not sure what, if any problems remain. If there is something else then please clarlfy for me.

Thanks, I will test this wit a Txt file that contains a few regulars then all the exceptions and see if it works
If I have to do "Маришка Тк" manually, so be it

WOW, everything worked except "Đuro ĐurićDušan Grujič" which should have been "Đuro Đurić" and "Dušan Grujič".
It may have failed with them right next to each other. I added son regular names befor and after the exception names. even "Маришка Тк" worked

I have attached your xlsm (renamed) and the text file I used.

OK, I just moved "Dušan Grujič" to another spot and the problem followed. "Dušan Grujič" and the name after were in put in the same cell.

Attached is an update that fixes the one last exception - "Đuro Đurić" was the culprit actually. The difficulty is that the VBA window does not fully support Unicode characters. The usual workaround to this is, in the VBA window, to choose an appropriate font for the language in question. We can't do that as you seem to have names in every language under the sun! As a result the copy/paste into the Exception List occasionally doesn't work correctly. I had to force the Exception List entry for "Đuro Đurić" to use the correct character codes for the "D" with a bar and the "c" with an acute accent as follows:

Formula:

Please Login or Register  to view this content.

Yes, the names are from around the world.
I'll test this further. May be a few days
Thanks for your help.
One other quick question.
I use conditional formatting to identify those names that are already on my master list.
Each time I add the names generated on this list to the master list, the conditional formatting changes and I have to redo the conditional formating. After identifying the copies of the names, I highlight the names to be removed and delete, moving the cells up.
Thx

OK I ran the latest file names_revD.xlsm. Works well for "Đuro Đurić" Tan my test.text file(58 names including all the ones that are currently erroring) and ALL the names on
my "exception list" worked except "Dušan Grujič". The odd thing is that when I use another file, exception names.txt, which has the same exceptions in it but has over 1600 names.
The ones that still error are Josef Pohořelský, Łukasz Kowalski, Ryszard Steć, Şükran Yücel, Zijad Numanović, and Dušan Grujič.
Odd that your macro works on a smaller file (except for Dušan Grujič) but errors on the larger txt file.

Also, how do you find the character codes for these type of characters?
I found a site called mauvecloud dot net. Gives the individual codes in Decimal Character Codes, Hexadecimal Character Codes, HTML with named character references, or a Java string
This could allow me to modify the ones that are not working in the manner that you did "Đuro Đurić"
Kewl, learning stuff

I've attached the files referenced!

OK, I think I have learned al LOT of stuff!
Using what you showed me I modified your macro with all the names on the list.
So far I have run about 6000 names including all the exception names
It worked PERFECTLY!
I named it (with your permission) Geoff_revE.xlsm
Thanks for your kind Help

Well, I had been preparing a reply but I think I will quit while I'm ahead - it's sounds like you have fully sorted yourself out Thanks for letting me know and let me know if anything else crops up.

Will do, and thanks for your help it is SO nice not to have to sort those names by hand.
Any thoughts on the conditional formatting changing after each run of 1700 to 2200 names?

Thanks for the added rep.

I'm not quite sure what you are trying to do with the conditional formatting. I think you are saying you have another Excel file that has your master list right? I'd need to see at least a representative subset of that workbook and understand your steps better and see the conditional formatting formulas you have used.