String Manipulation: Cleaning up address labels

[geekmee]

Doug,

Why does this Extract Last Name formula only return partial last name results?:

Right([FULLNAME],InStr(1,[FULLNAME]," "))

(see attched PDF)

 

[DALeffler]

Because the Instr function is returning the character position of the first space in FULLNAME from the left, and the Right function is taking that many characters from the right of FULLNAME. Let FULLNAME = "Mrs. Cindy Anderson". Then the first " " from the left is at character postion 5, and Right([FULLNAME],5) = "erson".

InstrRev looks for specified character occurence from the right side of the string to be searched and returns that character position: InstrRev("Mrs. Cindy Anderson"," ") returns 11. Note that numerical character positions are numbered from 1 to n, always from left to right.

To get the last name from FULLNAME, we need to know the character position of the 1st " " from the right side of FULLNAME and the total length of FULLNAME (we have to supply the Right function the number of characters to extract).

So:

Right([FULLNAME], Len([FULLNAME]) - InstrRev([FULLNAME]," "))

Try that.

 

[Mike375]

I had to change the way I was extracting middle name or initial if there was no full top. First time around I happened to have the right number of characters in the first name

I changed to this to get middle name and last name into a field so I could ten pull off the middle name

TryRt: Right(Trim([FullName]),Len(Trim([FullName]))-InStr(1,[FullName]," "))

 

[Mike375]

Is there is a way to stop #Error when Left([]) is applied to one word or character

John
.
etc

I did Right on Right on Right etc and then did Left on the various Right results. The query was limited to 6 or 7 of the calculated on calculated fields. I did the Lefts my making the Rights query a MakeTable.

The Lefts done on each of the Rights ends up seprarting every character and did not matter whether full stops/period wer after Mr etc and it did not matter if there was a space between Mr and period....Mr .

But #Error in the Left function when only one entity present is not good.

Is there some function etc that can tell if there is only one entity in the field. If so, then I could probably do the Lefts with IIf

 

[DALeffler]

Is there is a way to stop #Error when Left([]) is applied to one word or character

#Error in a query result in this case more than likely means the value resolved for the length argument is negative.

Any expression that resolves to a positive number - even one with decimal places - will usually work for length. Example:

?Left("testing", Iif(Instr(1,"testing","k") = 0, len("1234"),Instr(1,"testing","k")))

returns "test" when run in the immediate window...

hth,

 

[Mike375]

Doug,

I just stumbled on (InStrRev([TryRt2]," ")) gives 0 when only one entity.

So used IIf =0

Will that work the same as yours or will it miss a few scenarios.

This attachment has all the Lefts. I had do MakeTable from the Rights query as 7 of them was the maximum. In other word could not do all the Rights and Lefts in one query.

I think this get most scenarios. One exception is doing a name without any spaces....JohnAndrews.....Also, if there is more than once space then a Trim would need to be done.

geekmee

I am glad you made this posting as it has resulted in me extending and cleaniing up what I had. I can't believe I have been doing this Saturday

 

[Mike375]

A space in front of the original names is easily picked up by A-Z

Would there be any way to detect/select records with more than one space between names ie. John Andrews and John Andrews etc. Those spaces foul up the Rights and hence the Lefts.

 

[DALeffler]

InStr([TryRt2]," ") should also return 0. After all, there are no spaces in the string starting from either the left or right of the string.

Try nesting your Left or Right functions in an Iif:

Iif(Instr(1,[MyString]," "), Left([MyString],SomeNumber), [MyString])

 

[DALeffler]

Would there be any way to detect/select records with more than one space between names ie. John Andrews and John Andrews etc.

You can nest Instr functions to determine if there are 2 spaces in the string like so:

?instr(instr(1, "test test test", " ")+1,"test test test"," ")

returns 10, but

?instr(instr(1, "test testtest", " ")+1,"test testtest"," ")

returns 0 as does

?instr(instr(1, "testtesttest", " ")+1,"testtesttest"," ")

Nesting more than 2 Instr functions is possible, but complicated.

 

[Mike375]

Doug,

I have a general habit of breaking things up rather than nesting. Sometimes good, sometimes bad.

But I will try that one of yours. Probably tomorrow because I getting tired and maiking mistakes

Actually, my situation is the opposite of geekmee. Names we buy all come split in Excel columns.

However, names, addresses I have entered are done in one field because I found it is easier for people to enter that way. Then I break them up for any future referencing, sorting etc. We will get a few double spacings etc. The names we buy I joing them with & " " & because people doing phoning do better when looking at a John Smith as an entity. Also good for my Access/Word-Bookmarks

But after this thread and your last suggestion on nesting Right and Left I can probably knock it all over in one go.

I am quite happy with my last effort because it will handle some of our addresses with double worded suburbs which with state and postcode is four entities in Australia.

What I had prebiously would not handle

Watsons Bay NSW 2030

geekmee's thread has been very timely for me as I was spending the Christmas/New Year period cleaning what I had for this sort of stuff. It was all over the place, some bits in my main data base, bits in little data bases etc.

Thanks for help Doug on the nesting of Rights and Lefts as that will make it much neater.

Mike

 

[Mike375]

Using " " and " " etc in the Rights and Lefts does the strick for double spacing etc.

Like "* *" in the query finds them as well as Like "* *" And Like "* *"

I think I about have them all now as I have the uppercase for JohnAndrews etc

 

[geekmee]

Doug,

Right([FULLNAME], Len([FULLNAME]) - InstrRev([FULLNAME]," "))


This was right on the money!...

This formula extracted last names perfectly, no errors on all 331 records. I was able to import all records into Windows Address Book. My mother can now sort/delete and maintain her Christmas Card List.

Thank you so much for your time and clear explanations. This has been very gratifying learning experience!!

Thanks again.

 

[cardgunner]

Last name removed

I searched the forum and looked at the example db.

I could not find an instance to satisfy my need to omit the last and save the remainder
Examples

Alan Albert Wayne
Chris J Newman
Mr. Milton Allen
Mr. & Mrs. G. Borden
Paul McDonald
William B. Jones

Results:

Alan Albert
Chris J
Mr. Milton
Mr. & Mrs. G.
Paul
William B.

 

[cardgunner]

Sorry Got it Left([Fullname],InStrRev([Fullname]," "))