'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Compare a Number with a Formatted Account Number in Excel
Question details
The user needs to compare a standard 14-digit number with an identically valued text string that contains dashes for formatting (e.g., 17025550100601 vs. 17-02-55-50-1006-01).
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Data validation or reconciliation where account numbers from different sources have mismatched formatting.
- Observed behavior
- Direct cell comparison evaluates to FALSE because one value is numeric and the other is a text string containing dashed separators.
Identify which column contains your plain numbers and which contains the dashed account text. Ensure that neither column has hidden leading or trailing spaces by using the TRIM function if necessary.
Use the SUBSTITUTE Function to Remove Dashes
The most reliable method to compare these values dynamically without permanently altering your original data.
By utilizing the SUBSTITUTE function, you can strip the dashes out of the text string on the fly. Converting the result back into a number allows for a mathematically accurate comparison with the plain numeric value.
Click on an empty cell where you want the TRUE/FALSE comparison result to appear.
Type the formula =--SUBSTITUTE(C2,"-","")=A2, assuming C2 contains the dashed text and A2 contains the plain number.
Press Enter. The formula replaces the dashes with nothing, the double negative (--) converts the text to a number, and Excel returns TRUE if it exactly matches the numeric value in A2.
Format the Plain Number to Match the Text String
Convert the plain number into a dashed text format for a direct text-to-text comparison.
Use Find and Replace to Strip Dashes Permanently
A quick manual way to clean up formatted account text if you do not need to preserve the original dashed formatting.
Easily Compare and Clean Data with WPS Spreadsheet
WPS Spreadsheet provides powerful text functions, formatting tools, and a seamless interface to handle complex data reconciliation tasks like comparing mismatched account numbers effortlessly.
- 1. Open your dataset in WPS: Launch WPS Spreadsheet and open the workbook containing your mismatched account numbers.
- 2. Apply the comparison formula: Type =--SUBSTITUTE(C2,"-","")=A2 in an adjacent blank column to instantly check for matches.
- 3. Drag to fill: Click and drag the fill handle down to apply the comparison logic to your entire dataset.
Frequently Asked Questions
Why does Excel say my identical account numbers do not match?
Excel treats numeric values and text strings differently. If one account number is stored as a raw number and the other is a text string containing dashes, a direct cell comparison evaluates to FALSE. You must standardize their formats using formulas before comparing.
What does the double dash (--) do in the SUBSTITUTE formula?
The double negative (or double unary operator) forces Excel to convert a numeric text string into an actual number. Because the SUBSTITUTE function always outputs text, the -- converts that result back to a number so it can properly match your plain numeric account value.
Can I use conditional formatting to highlight mismatched account numbers?
Yes. Select your plain account numbers, go to Home > Conditional Formatting > New Rule, and choose 'Use a formula to determine which cells to format'. Enter a formula like =--SUBSTITUTE(C2,"-","")<>A2 to highlight the rows that do not match.
Will changing the cell format to 'Number' remove the dashes automatically?
No. If the dashes were manually typed into the cell, Excel permanently stores the value as a text string. Changing the cell format only affects how numbers are displayed; it does not remove manual text characters like dashes.
