Today we are here with the new and most useful Excel Tip for you all, It’s about formating Fnames to Lnames to Xnames in simple steps.
Many newbies working on Excel sheets always get confused with Fnames, Lnames and Xnames formates. While some get stuck in converting the whole Lnames to Xnames and Fnames in strings. These are some simple strings that can make your life easier.
All you have to do is put the right formula in the right cell to get the exact First Name, Last Name list out of your Contact Database.
How Excel Spread Sheet Works:
The Excel spreadsheet app lets you create, view, edit, and share your files with others quickly and easily. Create spreadsheets, data analyses, charts, budgets and more while you view and edit workbooks attached to email messages.
Edit documents for data analysis, accounting, finance, or any other fields with confidence. When on the go, your data and office move with you. Create charts, run data analysis and annotate your documents from your phone at any time.
Create formulas and spreadsheets, review your charts or start a budget on the go. Make spreadsheets and data files your way with robust formatting tools and great features. Excel lets you build your charts and spreadsheets to meet your specific needs.
Xname Lname Xname Fname Formate for Excel:
The Excel Sheets <Firstname LastName> to <LastName, FirstName> can be easily managed with this simple string.
Here is the formula for Xname Lname Xname in B2 Cell to easily convert the whole database sheet.
=RIGHT(SUBSTITUTE(TRIM(A2),” “,”~”,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2),” “,””))),LEN(SUBSTITUTE(TRIM(A2),” “,”~”,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2),” “,””))))-FIND(“~”,SUBSTITUTE(TRIM(A2),” “,”~”,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2),” “,””)))))&”, “&LEFT(SUBSTITUTE(TRIM(A2),” “,”~”,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2),” “,””))),FIND(“~”,SUBSTITUTE(TRIM(A2),” “,”~”,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2),” “,””))))-1)
Excel Sheet Xname Lname Xname Conversion:
To conveniently converting the Xname Lname Xname the procedure is given below.
- Need to switch Fname Lname or vise-versa, make sure to bring the sheet in place.
- Make sure to display the Last Name in separate Cells of your Excel Sheet.
- The smaller names the better conversion took place.
- 4-5 letter names mostly suites in Fnames
- Once the names are in place Put the above-mentioned Formula in Cell B2.
- Similarly, you can add the same formula in Google Sheets too.
Works with 2 Words Fnames or Lnames:
If you have a list of dr xname or dr Lname and you have 2 words or more in each column, either First name Column or Last Name Column. you can follow this pic to get them all sorted.
Need to switch “LName, FName” to “FName LName” try This:
If you have a worksheet that has the names listed as the Last Name, First Name in your database. And you want to convert it to First name Last Name format, you have to follow this little code in A1 of your list.
A1 = Smith, John
Returns: John Smith
Separate Column Spreadsheet:
FName ------------- Charlie MiddleInitial ------------- S LName ----------- Brown
SUBSTRING(Name, CHARINDEX(',', Name) + 1, CHARINDEX(',', Name + ',', CHARINDEX(',', Name) + 1) - CHARINDEX(' ', Name) - 1)
Download Script file for First name Last Name Conversion:
Download this file and make sure to add the following code to your Excel sheet.