How to Use VLOOKUP to Replace Text with Shorter Values in Excel
Question details
The user needs to replace long, repeated text strings in a dataset with shorter, standardized descriptions using a separate lookup table.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Cleaning or standardizing large datasets containing long text entries that have corresponding short codes or descriptions stored in a separate reference workbook or worksheet.
- Observed behavior
- The user wants to output the matching shorter value into a result cell dynamically based on the original long text, rather than performing manual find-and-replace actions.
Ensure your lookup table is organized with the original long text in the first column and the shorter replacement values in the second column. Exact-match VLOOKUP is sensitive to extra spaces, so verify that there are no hidden trailing spaces in your source data.
Use an Exact-Match VLOOKUP Formula
Use the VLOOKUP function with the FALSE argument to search for the original text in your reference table and return the corresponding short text.
The VLOOKUP function is ideal for mapping and replacing values dynamically without permanently altering the original dataset. By locking the lookup range with absolute references, you can easily apply the formula to an entire column.
Set up a two-column reference table on a new sheet or within your workbook (e.g., in cells B2:C4). Place the original long text in column B and the short replacement text in column C.
Click the result cell where you want the shorter text to appear. Type =VLOOKUP(B7, $B$2:$C$4, 2, FALSE), assuming B7 is the cell containing your source text and $B$2:$C$4 is your lookup table range.
Press Enter to apply the formula. Then, click and drag the fill handle (the small square at the bottom-right corner of the cell) down to apply the formula to the remaining rows in your column.
Easily Map and Replace Text with WPS Spreadsheet
WPS Spreadsheet is a powerful, free alternative for data analysis. It fully supports VLOOKUP and other advanced formulas, making it easy to dynamically replace long text with shorter values across massive datasets.
- 1. Open Data: Launch WPS Spreadsheet and open the workbook containing your dataset.
- 2. Set Reference Table: Create a reference table with your long text and the corresponding shorter values.
- 3. Apply Formula: Type the VLOOKUP formula using the FALSE parameter for exact matching and drag it down to replace all necessary values.
Frequently Asked Questions
Why is my VLOOKUP returning an #N/A error when replacing text?
This typically occurs if there is a mismatch between your lookup value and the reference table. Check for hidden leading or trailing spaces, and ensure the original text exactly matches the text in the first column of your lookup table. You can use the TRIM function to remove extra spaces before performing the VLOOKUP.
Can I use VLOOKUP to replace partial text within a cell?
VLOOKUP is designed to match the entire cell's content against the reference table. If you need to find and replace partial text or specific substrings within a cell, you should use the SUBSTITUTE function instead.
Is there a more modern alternative to VLOOKUP for text replacement?
Yes. If you are using newer versions of Excel or WPS Office, you can use the XLOOKUP function. It offers a simpler syntax, defaults to exact matches, and can look up values in any direction, making mapping and text replacement even easier.
