Hi,
I was just wondering if you can take a look at my text project file. Can you adjust the formula to include a title, a hyphen between two last names and a comma between last name and JR, IV, III, etc. I would appreciate any help that you can give me. The file should be attached. Thank you, Joe from Michigan.
depending how many real-life variables you have to deal with this may prove to be too complex for function based approach, and you might need to consider using Regular Expressions (beyond my competency)
attached is your sample file - with the various results
note: I have (deliberately) ignored the hyphen as I don't think you can guarantee bullet proof results e.g. Ashmun Baker should, perhaps, be Ashmun-Baker but Van Eaton should not be hyphenated
I would go further to say that in the case of hyphen etc it isn't really feasible to determined if John Ringo Starr should be John Ringo-Starr, or as-written -- the attached assumes as-written
regardless, hopefully this helps you to some extent.
Last edited by XLent; 12-02-2020 at 12:06 PM. Reason: reloaded attachment
Hi,
When I entered another address it says value error. I copied the formula from the top and not sure why this is causing this.
the errors in your file are unrelated to the new formulas but, rather, they result from the 2 new entries failing to adhere to your pre-existing "address rules", more specifically:
once modified, to adhere to above convention, all errors will resolve.3) State must have a comma before it
4) Town must have a dash before it
Oh ok, I see now. thanks.
Hi
One more thing if I can bother you on this. Can you look at the last two entries?
When I enter Miss and Ms.it goes to the first name column instead of the Title column.
Can you help fix this by putting it in the Title column? Mr. and Mrs. and Dr. are going in the right place.
I thank you for your help...Joe
Last edited by Joeman364; 12-03-2020 at 04:33 PM.
so the yellow section at the top of column B hold known "prefixes" (Dr., Mr. etc) so, add Ms. and Mrs to this list (B4:B5) and then modify B15 to:
=IFERROR(LOOKUP(9.99E+307,SEARCH($B$1:$B$5,LEFT($A15,FIND($F15,$A15)-1)),$B$1:$B$5),"")
before copying down
If you think these lists (yellow) will change, regularly, then it would be worth creating Dynamic Named Ranges, or storing in discrete Tables, and using those references in the relevant name/address related formulae.
Thanks, now its working. I really appreciate the advice you gave me on this project.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
Administrative Note:
Next time, make sure that your thread title conveys what your request is about. Tell us what you are trying to do, not how you think it should be done.
Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).
Please see Forum Rule #1 about proper thread titles.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Well, one more thing: how do you get two middle names in the last name column?
Administrative Note:
Welcome to the forum.
We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.
Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.
The thread started at https://chandoo.org/forum/threads/text-project.45396/ is not the same but is very closely related
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks