WebYou can use Find and Replace function in Excel to remove middle initial, too. 1. Select the name list and press Ctrl + F to open Find and Replace dialog. 2. Then click Replace tab, … WebMay 5, 2024 · One more shorter formula for you to try, =TRIM (MID (SUBSTITUTE (A1," ",REPT (" ",LEN (A1))),LEN (A1),LEN (A1))&" "&LEFT (A1,FIND (",",A1)-1)) Hope this …
Formula to remove every middle name in a cell in Excel
WebMar 26, 2010 · I tried this formula and it does the following. A Jones, Robert becomes, Jones, Robert, A If I have a name which includes the middle name it displays as follows Andy Jones, Robert displays as Robert, Andy I'm trying to remove the middle initial and/or middle name (if applicable) all together. Thanks, Tom WebMar 4, 2024 · SUBSTITUTE ($A1,",","") removes the comma, to give Gates Bill Henry. Then two bits like this: TRIM (MID (SUBSTITUTE (" "&$B1," ",REPT (" ",125)),125*COLUMNS ($A:A),125)) puts a space in front of Gates Bill Henry) and then substitutes 125 spaces for each space in " Gates Bill Henry". gene harris live at otter crest
TRIM function - Microsoft Support
WebWe will use a formula that combines the MID, FIND, and LEN functions to switch first and last names with commas in the following dataset. In our process, we follow the following steps: Select cell B2 and type in the following formula: =MID (A2&", "&A2,FIND (" ",A2)+1,LEN (A2)+1) Click the Enter button on the Formula bar to enter the formula. WebMar 23, 2024 · Press Ctrl + Space to select all cells in a column. Press Ctrl + H to open the " Find & Replace " dialog box. Press Space bar in the Find What field and make sure the " Replace with " field is empty. Click on the " Replace all " button, and then press Ok. Voila! All spaces are removed. Using formula to remove all spaces WebMar 25, 2004 · in these examples i want to extract the middle initial which is the letter "C" and put it into a new column, then delete the middle initial from the original column so that only the first name remains. However, not all middle initials have a period(.) after them - so the formula/macro must be able to work in both situations. deadly items ddo