How to Create JPEG Filenames from First and Last Names in Excel
Question details
The user needs to generate JPEG filenames in Lastname_Firstname.jpg format from separate first-name and last-name columns for a later SQL import.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A user has first names and last names in separate columns and needs an additional filename column that follows the Lastname_Firstname.jpg naming convention.
- Observed behavior
- A new column should contain the required JPEG filenames so the data can be exported or imported into SQL without manual retyping.
Make sure the first name and last name are in separate columns, such as column A for first names and column B for last names. Check the list for extra spaces or blank cells before filling the formula down.
Use the ampersand operator to create JPEG filenames
This is the quickest way to join the last name, an underscore, the first name, and the .jpg extension into one filename.
The & operator concatenates the values exactly as written. If first names are in column A and last names in column B, the formula in row 2 is =B2&"_"&A2&".jpg".
Click the cell where you want the first JPEG filename to appear, for example C2.
Type =B2&"_"&A2&".jpg" and press Enter. The cell displays Lastname_Firstname.jpg.
Drag the fill handle from the lower-right corner of C2 down through the rows that contain names, or double-click the fill handle to auto-fill the list.
Use the CONCATENATE function to build the filename
Use this method if you prefer a function-based approach or are working with older spreadsheet versions.
Build Lastname_Firstname.jpg filenames in WPS Office Spreadsheets
Open your Excel-compatible workbook in WPS Office Spreadsheets, use the same concatenation formulas you already know, and generate the required JPEG filenames for SQL import without paying for Microsoft Excel.
- 1. Download WPS Office: Go to the WPS Office website and download the free Windows, Mac, or Linux version.
- 2. Open the workbook: Open the .xlsx or .xls file containing the first-name and last-name columns in WPS Spreadsheets.
- 3. Create the filename column: In C2, enter =B2&"_"&A2&".jpg" and press Enter.
- 4. Fill the formula down: Drag the fill handle down the filename column to apply the formula to all rows.
- 5. Save the file: Save the workbook as an Excel-compatible .xlsx file or export it to CSV for SQL import.
Frequently Asked Questions
What formula creates Lastname_Firstname.jpg from first and last name columns?
If the first name is in A2 and the last name is in B2, use =B2&"_"&A2&".jpg". This returns a filename such as Doe_John.jpg.
Can I use CONCATENATE instead of the ampersand operator?
Yes, use =CONCATENATE(B2,"_",A2,".jpg"). CONCATENATE and & produce the same filename string when the cell references and text segments match.
How do I apply the filename formula to every row?
Enter the formula in the first row, then drag the fill handle down the column or double-click it. In Excel 365 and supported dynamic array versions, you can use a range formula like =B2:B1000&"_"&A2:A1000&".jpg".
Does WPS Office support these Excel filename formulas?
WPS Office Spreadsheets supports the & operator, CONCATENATE, and Excel file formats, so you can open an .xlsx workbook and use the same filename formulas.
How can I remove extra spaces from the generated filenames?
Wrap each name reference in TRIM, for example =TRIM(B2)&"_"&TRIM(A2)&".jpg". This removes leading, trailing, and extra internal spaces before the filename is created.
