I need help with this formula, i've spend WAY too much time on it

Options

I am trying to extract names from a contact list, some have periods, some just one name some have multiple names. I'm trying to extract them so it says F. Jones or F.B. Jones or B G Jones

when i use the formula i have, it works for names with a period, but the other ones it gives the entire first name.. help!

the blank columns i would like to say F. Jones or F.B Jones, or F L Jones



Answers

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Options

    @Roxana_u

    I believe there are way too many ways someone could write or abbreviate their name that you're going to have a very hard time writing a formula to clean it up and display it in a single rigid way. When you run into something like this that requires such a complex formula, it's best to take a step back and see if there's a way to accomplish what is needed in another way.

    For example if someone is filling out a form and these are the results you're left with for names, maybe it would be best to provide a drop-down box where they select their name from a list so that you're always gathering the name in the end format you need.

    If you still require this formula, I wish you the best of luck. I'm not sure how you'd anticipate for all the ways someone could write it.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!