Hi My Online Training Hub Experts,
In my text project, Can you update to put a formula in the prefix column to reflect a prefix such as Mr. Mrs. Miss, Dr. etc. Also I would like to update the formula in the last name column so that it will have a hypen between two last names, but not between the last name and "Jr. or IV". I appreciate your help on this issue. Hope all is well and safe. Thank you. Joe, from Michigan.
Doesn’t seem to be a great deal to work off
I can’t see a way to assign Mr/Mrs/Miss etc as its not in the data as we don’t even know the sex of the person to have a guess at Ms or Mr.
The Hyphen part is maybe possible but not particularly tidy or effective - the below works out if the last part of the name is less than 4 characters and then insets a - for any names longer.
=IFERROR(IF(LEN([@[Last Name]])-FIND(" ",[@[Last Name]])>4,SUBSTITUTE([@[Last Name]]," ","-"),""),"")
So, if we have a name like Smith Ike this would miss them out and also not sure if Mark Gorteski Junior should be hyphenated or not?
Purfleet
Hi Purfleet,
Thanks for responding to my post. I'll add the Mr. and Mrs, etc to the data. The formula you provided me resulted in a 0 in the last name column. Joe, from Michigan.
It cant go in the Last Name column as that is the columns with the starting data. I put it at the end.
Thanks Purfleet for your help and time.....works good.
No problems, but be careful with it - its not a perfect fix