How to Return Text Before a Hyphen from a VLOOKUP Result in WPS Spreadsheets
Question details
The user needs to extract only the characters before a hyphen from a VLOOKUP return value such as 7.52-14.01, where the prefix length may vary.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A VLOOKUP formula returns a combined text value containing a hyphen, and the user wants a separate cell showing only the part before the hyphen.
- Observed behavior
- The formula currently returns the full string, including the hyphen and suffix, rather than only the leading portion.
Open the workbook in WPS Spreadsheets and confirm which cells contain the VLOOKUP results. Make sure the hyphen is consistently present in those results so FIND can locate the delimiter.
Use LEFT and FIND to Extract Text Before the Hyphen
Use this method in any WPS Spreadsheets or Excel version when your VLOOKUP result always contains a hyphen.
FIND returns the position of the hyphen within the result, and LEFT returns that many characters minus one. This approach works even when the prefix length varies.
Click the cell where you want the extracted value to appear and start typing the formula.
Type =LEFT(VLOOKUP($G3,'Quicken Portfolio'!$B$12:$W$38,9,FALSE),FIND("-",VLOOKUP($G3,'Quicken Portfolio'!$B$12:$W$38,9,FALSE))-1).
Press Enter to calculate the result, then use the fill handle to copy the formula to the remaining rows.
Use TEXTBEFORE for Newer WPS Office or Microsoft 365 Versions
Choose this shorter formula when your spreadsheet app supports the TEXTBEFORE function.
Extract Text Before a Hyphen from a VLOOKUP Result in WPS Spreadsheets
Use WPS Spreadsheets to clean up VLOOKUP results without changing the source data. The LEFT and FIND combination, or TEXTBEFORE on supported builds, lets you split values like 7.52-14.01 while keeping full .xlsx and .xls compatibility.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, click Spreadsheets, and open the file that contains the VLOOKUP results.
- 2. Select the output cell: Click a blank cell next to the VLOOKUP result where you want the extracted text to appear.
- 3. Enter the extraction formula: Type =LEFT(VLOOKUP($G3,'Quicken Portfolio'!$B$12:$W$38,9,FALSE),FIND("-",VLOOKUP($G3,'Quicken Portfolio'!$B$12:$W$38,9,FALSE))-1) and press Enter.
- 4. Copy the formula to other rows: Drag the fill handle down to apply the same extraction to additional VLOOKUP results.
Frequently Asked Questions
Why does VLOOKUP return the full string instead of only the value before the hyphen?
VLOOKUP returns the entire contents of the matching cell. The LEFT and FIND formula reads that returned string, finds the hyphen position, and extracts only the characters before it.
Can I use TEXTBEFORE in WPS Spreadsheets?
Recent WPS Office versions include TEXTBEFORE. If typing =TEXTBEFORE(VLOOKUP(...),"-") returns #NAME?, your version does not support it, so use the LEFT and FIND method instead.
How do I avoid an error when a returned value has no hyphen?
Wrap the formula in IFERROR: =IFERROR(LEFT(VLOOKUP(...),FIND("-",VLOOKUP(...))-1),VLOOKUP(...)) returns the original value instead of #VALUE!.
Does this method work if the text before the hyphen has different lengths?
Yes. FIND locates the hyphen position for each cell, and LEFT uses that position minus one, so it adapts to variable prefix lengths.
Can I extract text after the hyphen instead?
Yes. Use MID with FIND and LEN, or use TEXTAFTER if your spreadsheet version supports it.
