I found this technique on the web somewhere several years ago and it works great.The task can be accomplished in a few different ways - by using the Text to Columns feature, formulas, and Split Names tool.The Data preview section shows that all of our names are parsed just fine.
However, if you plan to make any changes to the original names and are looking for a dynamic solution that will update automatically, youd better divide names with formulas. This number is supplied to the LEFT function as the number of characters to be extracted, starting on the left side of the string. After that, you subtract the above number from the total string length to get the length of the last name, and have the RIGHT function extract that many characters. This number goes directly to the numchars argument of the RIGHT function indicating how many characters to extract from the end of the string. Then, you supply the above number to the RIGHT function instructing it to pull that number of characters from the end of the string. To determine the position of the first space, use a simple SEARCH(,A2) function, to which you add 1 to start the extraction with the next character. ![]() To work out the length of the middle name, you subtract the position of the 1st space from the position of the 2nd space, subtract 1 from the result to get rid of a trailing space, and put this number in the numchars argument of MID, telling it how many characters to extract. Calling Names In Excel Download Our SampleTo better understand and probably reverse-engineer the formulas, you are welcome to download our sample workbook to Separate Names in Excel. If Excel senses a pattern (in most cases it does), it will populate the first names in all other cells automatically. If it does not work in your Excel, click the Flash Fill button on the Data tab Data tools group. If it still doesnt work, then go to File Options, click Advanced, and make sure the Automatically Flash Fill box is selected under Editing options. If you are dealing with different name formats, the above methods will mess up your worksheets by putting some name parts in wrong columns or returning errors, for example. ![]() Calling Names In Excel Code That WeIf the tool meets your expectations and you decide to get a license, dont miss the 15 off coupon code that we provide especially for our blog readers: AB14-BlogSpo. Its pretty fast and the end result is that each name is in a separate cell and you can do what you like with them at that point. Select the names, click the Text-to-Columns button on the ribbon. Youll see the data in that window and you should select the Delimited button click Next Choose the Space button, click Next Choose the General button click Finish. In the case of that second name that appears not to have a middle name, youll have to cut and paste the Kumar part into the last column. You can do a LEFT and then a RIGHT LEN splitting formula, but I dont think that would be much quicker. However, if the name list is being lengthened frequently, you might want to use another approach.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
December 2020
Categories |