_:na68088b1ccc2442686e18ae8d9c429dcxb7
<http://schema.org/text>
"The LET function allows you to use variables in your formula. I used the variable \"t\" to be SUBSTITUTE(B5,\" & \",\"/\")\nThe definition of t in the formula is what follows the t between the two commas as shown below.\n=IFERROR(TRIM(SUBSTITUTE(LEFT(B5,FIND(\" \",B5&\" \",FIND(\",\",B5)+2)-1),F5&\",\",\"\")),LET(t,SUBSTITUTE(B5,\" & \",\"/\"),SUBSTITUTE(LEFT(t,FIND(\" \",t)-1),\"/\",\" & \")))\nThat is, t is the same as the original data but replaces any \" & \" with \"/\"\nI wanted to eliminate the those spaces around the \"&\" so that I could use remaining spaces to help identify the various parts of the name.\n\n\nThe 50 is part of replacing all spaces in the name with 50 consecutive spaces. This only applies in cells that have no comma, meaning that the Last name is the last word in the text.\nHaving inserted those 50 spaces, the formula then takes the right hand 50 characters then trims off all the spaces. this leaves you with the last word from the text - provided that word is not longer than 50 characters. If you did expect any names to be longer than 50 characters you could increase the two 50's near the end of the formula to a bigger number.\n\n\nThe best answer is as I said before. ;)\n\n\n\nHowever, failing that, try these.\n\nBTW, I hope that you don't get any names like \"Vincent van Gogh\" or \"Michael van der Veen\"\n\nABCDEF12CurrentDesired3NameFirstMiddleLast45John SmithJohnSmith6Smith, JohnJohnSmith7John F. SmithJohnFSmith8John F SmithJohnFSmith9John Flynn SmithJohnFlynnSmith10Smith, John F.JohnFSmith11Smith, John FJohnFSmith12Smith, John FlynnJohnFlynnSmith13John & Jill SmithJohn & JillSmith14John/Jill SmithJohn & JillSmith15Smith,JohnJohnSmith16Smith,John FJohnFSmith17Smith,John F.JohnFSmithD5:D17D5=IFERROR(TRIM(SUBSTITUTE(LEFT(B5,FIND(\" \",B5&\" \",FIND(\",\",B5)+2)-1),F5&\",\",\"\")),LET(t,SUBSTITUTE(B5,\" & \",\"/\"),SUBSTITUTE(LEFT(t,FIND(\" \",t)-1),\"/\",\" & \")))E5:E17E5=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5&\", \",F5&\",\",\"\",1),\"/\",\" & \"),\",\",\" \"),\".\",\" \"),D5&\" \",\"\",1))F5:F17F5=IFERROR(LEFT(B5,FIND(\",\",B5)-1),TRIM(RIGHT(SUBSTITUTE(B5,\" \",REPT(\" \",50)),50)))\n\n\nYou are Amazing.... Thank you.\n\nThe whole purpose of this exercise is to clean up the data. If we encounter names like \"Vincent van Gogh\" or \"Michael van der Veen\" then those can be looked at manually. However, with 50K+ records, it may be difficult to spot them easily. I guess this is something that needs to be corrected manually. I don't think anyone would mind fixing 1% of the data manually if 99% of the data is correct.\n\nOnce again. Thanks for your help.\n\nRegards,"
<https://www.mrexcel.com/board/threads/text-to-column-using-a-formula.1188359/>