How to Match the First Character and Combine Values in Excel using VBA or TEXTJOIN
Question details
The user wants to find and combine multiple Excel cell values from a specific range where the first character matches a given lookup value.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Extracting and concatenating text strings from a list based on their starting letter, returning a single, comma-separated result.
- Observed behavior
- The user requires a method, either through a custom VBA function or an advanced formula, to iterate through the data range, match the first letter case-insensitively, and join the matching values with a delimiter.
Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) if you intend to use the VBA solution. If opting for the formula method, check whether your spreadsheet software supports the TEXTJOIN function.
Use a Custom VBA Function (UDF)
Create a User-Defined Function in VBA to loop through a range, compare the first character, and concatenate the results. This is the most reliable method if your Excel version lacks TEXTJOIN.
By writing a custom VBA script, you can create a tailored function (e.g., vlookupextra) that behaves just like a native Excel formula but contains complex looping logic to match specific string characters.
Press Alt + F11 on your keyboard to open the Microsoft Visual Basic for Applications editor.
Click on 'Insert' in the top menu bar and select 'Module' to create a new blank script window.
Write a VBA function that accepts the lookup value, the target range, and a delimiter. Use a 'For Each' loop to iterate through the cells, checking if 'UCase(Left(cell.Value, 1))' matches your lookup character.
Return to your worksheet, select a blank cell, and enter your custom formula, such as =vlookupextra(A1, $B$1:$B$9, ", "), then press Enter.
Use the TEXTJOIN and LEFT Array Formula
Use built-in Excel functions to extract the first character and join the matching results without writing any VBA code.
Match Characters and Combine Data Easily in WPS Office
WPS Spreadsheet fully supports advanced array formulas like TEXTJOIN and provides a robust VBA/Macro environment, making it incredibly easy to extract, match, and combine complex text data without compatibility issues.
- 1. Open Your Data: Launch WPS Spreadsheet and open your existing Excel workbook containing the lookup values.
- 2. Apply Array Formulas: Directly type the =TEXTJOIN formula into your desired cell. WPS handles modern array formulas seamlessly.
- 3. Enable Macros if Needed: Navigate to the Developer tab to access the VBA Editor and write or run your custom text-matching scripts.
Frequently Asked Questions
Why does the TEXTJOIN formula return a #NAME? error?
This error appears if your current spreadsheet software version does not support the TEXTJOIN function. It is natively available only in newer versions (Excel 2019 and later). If unsupported, you should use the VBA solution instead.
How can I make the first character match strictly case-sensitive?
If using the formula approach, replace UPPER(LEFT(range,1)) with EXACT(LEFT(range,1), A1) to enforce case sensitivity. In VBA, use vbBinaryCompare instead of vbTextCompare during your string comparison.
Can I use a different delimiter besides a comma?
Yes. In both the VBA function parameters and the first argument of the TEXTJOIN formula, you can replace ", " with any text string you prefer, such as "-", " | ", or even CHAR(10) for a line break.
Do I always have to press Ctrl+Shift+Enter for array formulas?
Not always. While older spreadsheet software requires Ctrl+Shift+Enter to evaluate arrays properly, modern software with dynamic array support (like Microsoft 365 or updated WPS Spreadsheet) allows you to just press Enter.
