How to look up IDs that are formatted differently in Excel

Q: How do I remove dashes and spaces?

Nest SUBSTITUTE: SUBSTITUTE(SUBSTITUTE(A2:A7,"-","")," ",""). In Google Sheets, REGEXREPLACE(A2:A7,"[- ]","") removes several characters at once.

Q: How do I match IDs with and without leading zeros?

Convert both to numbers with --, or pad both with TEXT, for example TEXT(F1,"000000").

Formula (Excel and Google Sheets)

=XLOOKUP(SUBSTITUTE(F1,"-",""),SUBSTITUTE(A2:A7,"-",""),C2:C7,"Not found")

Strip the characters that differ from both sides inside the lookup: =XLOOKUP(SUBSTITUTE(F1,"-",""),SUBSTITUTE(A2:A7,"-",""),C2:C7,"Not found").

Result on the example: $23.75 in F2
Works in: Excel 2021, 2024 and Microsoft 365
Google Sheets: Same formula

See it work

The formula is already in F2. Change any input and the result updates, or edit the formula to experiment.

How it works

SUBSTITUTE removes the dashes, so TR-2207-B becomes TR2207B. Doing it to the whole lookup range inside XLOOKUP compares cleaned values on both sides, without a helper column or changing the source data.

=XLOOKUP(SUBSTITUTE(F1,"-",""),SUBSTITUTE(A2:A7,"-",""),C2:C7,"Not found")

Result23.75

F1"TR2207B"
The value in F1.
SUBSTITUTE(F1,"-","")"TR2207B"
SUBSTITUTE: Replaces matching text.
- textF1"TR2207B"
- old_text"-"
- new_text""
A2:A76 rows × 1 col: "TR-1104-A", "TR-1388-C", "TR-2207-B", "TR-2290-A", …
A range: 6 cells in one column.
SUBSTITUTE(A2:A7,"-",""){6 rows × 1 col} "TR1104A", "TR1388C", "TR2207B", "TR2290A", …
SUBSTITUTE: Replaces matching text.
- textA2:A76 rows × 1 col: "TR-1104-A", "TR-1388-C", "TR-2207-B", "TR-2290-A", …
- old_text"-"
- new_text""
C2:C76 rows × 1 col: $42.50, $68.90, $23.75, $54.00, …
A range: 6 cells in one column.
XLOOKUP(SUBSTITUTE(F1,"-",""),SUBSTITUTE(A2:A7,"-",""),C2:C7,"Not found")23.75
XLOOKUP: Searches one range and returns the matching item from another.
- lookup_valueSUBSTITUTE(F1,"-","")"TR2207B"
- lookup_arraySUBSTITUTE(A2:A7,"-",""){6 rows × 1 col} "TR1104A", "TR1388C", "TR2207B", "TR2290A", …
- return_arrayC2:C76 rows × 1 col: $42.50, $68.90, $23.75, $54.00, …
- if_not_found"Not found"

In Google Sheets

The same formula works in Google Sheets: =XLOOKUP(SUBSTITUTE(F1,"-",""),SUBSTITUTE(A2:A7,"-",""),C2:C7,"Not found"). We ran it in Google Sheets mode on the same data and it returns $23.75.

Other ways to do it

Each one was run on the same example sheet.

Formula	Result	When to use it
=INDEX(C2:C7,MATCH(SUBSTITUTE(F1,"-",""),SUBSTITUTE(A2:A7,"-",""),0))	$23.75	For Excel 2019 and earlier (Ctrl+Shift+Enter in 2016 and older).

Common mistakes

Clean both sides. Cleaning only the lookup value still fails against the dashed list.
SUBSTITUTE returns text. If one side is a number, like 22070, convert both to text with &"" before comparing.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Look up IDs that are formatted differently

The scanner drops the dashes from part numbers. In F2, return the price for the scanned code in F1 by matching it to the dashed part numbers in column A.

Questions

How do I remove dashes and spaces?

Nest SUBSTITUTE: SUBSTITUTE(SUBSTITUTE(A2:A7,"-","")," ",""). In Google Sheets, REGEXREPLACE(A2:A7,"[- ]","") removes several characters at once.

How do I match IDs with and without leading zeros?

Convert both to numbers with --, or pad both with TEXT, for example TEXT(F1,"000000").

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.