_:na68088b1ccc2442686e18ae8d9c429dcxb9
<http://schema.org/text>
"The best thing would be to get the original data cleaned up and provided in a uniform format.\nIf that is not possible, this is the best I can come up with based of those sample data formats.\n\nABCDEF12CurrentDesired3NameFirstMiddleLast45John SmithJohnSmith6Smith, JohnJohnSmith7John F. SmithJohnFSmith8John F SmithJohnFSmith9John Flynn SmithJohnFlynnSmith10Smith, John F.JohnFSmith11Smith, John FJohnFSmith12Smith, John FlynnJohnFlynnSmith13John & Jill SmithJohn & JillSmith14John/Jill SmithJohn & JillSmithD5:D14D5=IFERROR(SUBSTITUTE(LEFT(B5,FIND(\" \",B5&\" \",FIND(\",\",B5)+2)-1),F5&\", \",\"\"),LET(t,SUBSTITUTE(B5,\" & \",\"/\"),SUBSTITUTE(LEFT(t,FIND(\" \",t)-1),\"/\",\" & \")))E5:E14E5=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5&\", \",F5&\", \",\"\",1),\"/\",\" & \"),\",\",\" \"),\".\",\" \"),D5&\" \",\"\",1))F5:F14F5=IFERROR(LEFT(B5,FIND(\",\",B5)-1),TRIM(RIGHT(SUBSTITUTE(B5,\" \",REPT(\" \",50)),50)))\n\n\nThank you .... Absolutely fantastic ...... You are a life saver ......... This works. However, a few questions:\n\nWhat does \"t\" represent in the formula in D5?\nWhat is the significance of 50 in the F5 formula?\nHow do I modify this formula if the name is written as \"Smith,John\" or \"Smith,John F\" or \"Smith,John F.\"?\n\nAs I said earlier, you are a life saver even if there is no solution to the newly added option (Smith,John or Smith,John F or Smith,John F.).\n\nThanks."
<https://www.mrexcel.com/board/threads/text-to-column-using-a-formula.1188359/>