String Manipulation: Cleaning up address labels

[geekmee]

PROBLEM: String Manipulation

"Cleaning up Mom's Christmas address labels"

I need guidance on the best string manipulation functions (Instr, Left, Right) to cleanup my mother's Christmas address list of 300+ names.

I have successfully imported the text file into Excel and exported to Access; fieldnames: FULLNAME, ADDRESS, CITYSTATEZIP

I have found instructions on how to breakout FULLNAME field into FIRSTNAME and LASTNAME.

But within the FULLNAME field are many combinations of titles (Mr., Mr. & Mrs., Dr., HON.) with inconsistent periods applied.

Which one of string manipulation functions:

• Instr
• Left
• Right

would be best for extracting these various titles from this name field?

I understand the concepts behind the above functions, but not enough experience using them to understand the tedious syntax or which string manipulation function would be best for extracting the varying title entries to a separate created field called TITLES.

So far, I have deduced this will be a multi-step process. But asking for guidance:

1.) Which string function is best suited for this?
2.) Example of the function syntax for an update query?
2.) Suggested order to administer update queries?...

to extract misc titles from the FULLNAME field.

I am a novice-casual Access user.

Thanks, Greg


(If someone would copyright these steps into a book called "Cleaning Up Mom's Christmas Address List"... I am sure they could retire from sales on Amazon.

 

[geekmee]

Links to information would be fine... but just need simple tutorial-like instructions.

Instructions I have found so far (Microsoft Online) has been too overwhelming.

 

[geekmee]

Extract/split lastname?

How do I extract/split only the last name from a field?

I have a field FULLNAME with over 300+ entries in the following format:

Mr. Milton Allen
Mrs. Daisy Brown
Mr. & Mrs. G. Borden
Rev. & Mrs. C. Starks
Dr. & Mrs. Julius Coleman

In the field FULLNAME, what does a (update?) query look like that would extract characters for the last name into a separate column/field LASTNAME.


-Greg

(My thinking is, it might be easier to approach the FULLNAME field from the right side and work my way left characters towards the title or salutions, instead of starting on the left hand side of the field attempting to extract titles and salutations from the FULLNAME field.)
-

 

[boblarson]

I'm thinking you may have better luck just opening the file in Excel and using the Text To Columns feature and then do some manual adjustment. It may be faster than trying to build the code, or set of code, to do it all automatically.

 

[geekmee]

You are exactly right... this is turning into rocket science.

Thanks for saving me tons of time!

-G

 

[pbaldy]

You might find this of interest. Note the credit goes to Blindman.

 

[Mike375]

Greg

I now have a fairly full collection these things. In fact thanks to Bob and another bloke on dbforums my collection is about complete.

Is the list you put up (below) the full range of entries. If so I will have a go at them and that way see if my collection is fully complete. I will do them a calulated fields in a query with one field feeding off another. You can really waste some time with this stuff

Mr. Milton Allen
Mrs. Daisy Brown
Mr. & Mrs. G. Borden
Rev. & Mrs. C. Starks
Dr. & Mrs. Julius Coleman

 

[geekmee]

Unfortunately, Text To Columns appears to be working the field from Left to right which is giving my consistent results for the first few consistent characters "Mr." or "Mrs."... but the remaining words are being scattered across different columns on each record, because each field record has different count of words per record.

Each of the following records gives me different column breakouts:

Mr. Milton Allen
Mrs. Daisy Brown
Mr. & Mrs. G. Borden
Rev. & Mrs. C. Starks
Dr. & Mrs. Julius Coleman
Sandra & Tammy Coleman

What I need is to work the field name from Right to Left until it encounters the first space as the last name delimiter.

-G

 

[DALeffler]

Try Running this on a COPY of your table:

UPDATE MyTable SET MyTable.LastName = Mid([MyTable].[Fullname],InStrRev([MyTable].[FullName]," ")+1), MyTable.FullName = Mid([MyTable].[FullName],1,InStrRev([MyTable].[FullName]," ")-1);

It updates the LastName field to the characters after the first space from the right in FullName AND updates the FullName field by excluding the characters after the first space from the right.

 

[geekmee]

Yep, ....except for "Hon." for Judge and the names with no titles (null).

 

[DALeffler]

Fix those that didn't go, then run the query again (still using the COPY of your original data) but with one small alteration:

UPDATE MyTable SET MyTable.FirstName = Mid([MyTable].[Fullname],InStrRev([MyTable].[FullName]," ")+1), MyTable.FullName = Mid([MyTable].[FullName],1,InStrRev([MyTable].[FullName]," ")-1);


That should get the majority of 'em.

 

[geekmee]

TITLEUPDATE is the name of the copied database.

I did a search a replace on your formula to replace MyTable with table TITLEUPDATE.

I copied the following formula into the Update Query "Update To"

UPDATE TITLEUPDATE SET TITLEUPDATE.LastName = Mid([TITLEUPDATE].[Fullname],InStrRev([TITLEUPDATE].[FullName]," ")+1), TITLEUPDATE.FullName = Mid([TITLEUPDATE].[FullName],1,InStrRev([TITLEUPDATE].[FullName]," ")-1);

It returns an error message that I need to enclose something in quotes....then puts the flashing cursor on the following comma located after the parentheses:
"....+1), TITLEUPDATE.Fullname=Mid([TITLEUPDATE].[Fullname]..."

Please advise?

-Greg

 

[geekmee]

Syntax error message was: "...invalid syntax, or you need to enclose your text data in quotes."

"You may have entered an invalid comma or omitted quotation marks."

 

[Mike375]

I think this query has most of them (attachment)

You would need to add a few fields to the table and the filter/copy/paste etc

Actually if I already have the names split into title, first name and second name then I jpin them in one field for mail merge etc so as to have less bookmarks

 

[DALeffler]

Did you paste the SQL into the SQL window or the query design grid? It should go in the SQL window.

Create a new query in design view.

Close the Show Tables window.

Under the View Menu, select SQL.

Delete any text in the window.

Paste the first SQL into that window.

Under the View Menu, select Design.

The query is now ready to run.

Be careful! Once you run it, no way to easily revert back to the way it was.

Before you run the query for the 2nd time, CHECK THE TABLE for proper updates, then go back to the SQL view and make the change I highlighted in bold.

 

[Mike375]

This attached separates middle initial if no full stop and middle name

John Charles Wayne = Charles
Bill J Andrews = J

 

[geekmee]

Doug,

Query#1... 99% Successs!! (all but about 4 records)

Correct, I originally ran the first query in design grid...went SQL view for success.

 

[geekmee]

Doug,

Query Round#2... 99% Success (5 records unprocessed due to "lock violations")

Don't know what caused those skipped records??

Great work. Thanks!... and my mother thanks you!

 

[DALeffler]

Very Good!

Good luck with your project!

 

[geekmee]

Doug,

If I could pick your query skills to help me understand the following formula;

Before you sent me your query, this is what I found at: www.techonthenet.com...

http://www.techonthenet.com/access/queries/extract_lastname.php

==================================================
The following formula will extract the last name value from the

CustomerName field:

Left([CustomerName],InStr([CustomerName],",")-1)

This formula finds the first occurrence of a comma using the Instr

function. It then uses the Left function to extract the
characters from the CustomerName field up to (but not including) the

comma.

=====================================================

....and was trying to apply.

The above formula only works when the data value is organized: [Smith, John]

The formula seems to work left to right. I tried to reverse it to work right to left and look for the first blank space.

I tried substituting 'Right' function, and blank space between quotes ' " " '

The results returned only partial last names?

Any insight why this formula didn't work??

I thought this formula could be used to work Right-to-Left to extract the remaining data from my FULLNAME field and CITYSTATEZIP.