How to MATCH numbers formatted as text in Excel

You will sometimes encounter errors while trying to MATCH or LOOKUP data in Excel if your numbers are formatted as numbers in one of your tables, and as text in another table.

While special formats are available in Excel, they are relatively rarely used and are limited. Some “numbers” such as identification numbers are more often stored as text. This is done in order to add leading zeros, hyphens and other characters to that numbers. However, if we try to MATCH or LOOKUP those numbers stored and/or formatted as text with actual numbers, we will encounter issues.

Consider the following example:

number formatting in excel

Given that number 3 in the B6 cell is stored and formatted as text, we encounter an error when we try to find a match for the number 3 in the column B.

 

Now consider the following example:

how to MATCH numbers formatted as text in Excel example

In the column A we have user numbers, formatted as numbers.

In the column E we have user numbers, formatted as text.

We have to fill the column C with the mail addresses from column F, matching user numbers from column A with those from column E.

Obviously, we will immediately encounter errors if we just plain INDEX MATCH the data:

how to MATCH numbers formatted as text in Excel INDEX MATCH

In order to successfully match data, we should instead first take the number we are trying to match, and format it in a way our data source has it formatted. In this particular example, we can accomplish that with the help of the TEXT function:

how to MATCH numbers formatted as text in Excel INDEX MATCH TEXT

We have successfully matched our “transformed” lookup value with the values in the column E with the help of the TEXT function.

 

Now consider the reverse example:

how to MATCH numbers formatted as text in Excel example 2

In the column A we have user numbers, formatted as text.

In the column E we have user numbers, formatted as numbers.

We have to fill the column C with the mail addresses from column F, matching user numbers from column A with those from column E.

Obviously, we will also immediately encounter errors if we just plain INDEX MATCH the data:

how to MATCH numbers formatted as text in Excel INDEX MATCH

In order to successfully match data, we should instead first take the text we are trying to match, and convert that text to number, given that our data source has user codes formatted as numbers. We can accomplish that with the help of the VALUE function:

how to MATCH numbers formatted as text in Excel INDEX MATCH VALUE

Had we’ve just been dealing with numbers formatted as text, or numbers containing leading zeros, we would have been successful. However, our numbers formatted as text also contain characters (in this case, hyphens), which the VALUE function can’t handle.

We will first have to create a new text string without text characters, and only then can we convert that new text string to number. There are several ways to do this, the basic one being with the help of the LEFT, MID and RIGHT functions:

how to MATCH numbers formatted as text in Excel INDEX MATCH VALUE LEFT MID RIGHT

We have successfully created a new text string that contains just digits, converted that text string to number with the help of the VALUE function, and then matched our number with the values in the column E.

In newer versions of EXCEL, we can further simplify this by using the SUBSTITUE function and generating our new text string by replacing hyphens with nothing:

how to MATCH numbers formatted as text in Excel INDEX MATCH VALUE SUBSTITUTE

Here, we have again successfully created a new text string that contains just digits, converted that text string to number with the help of the VALUE function, and then matched our number with the values in the column E.

 

Dig deeper:

Lookup with unique identifiers

Leave a Reply

Your email address will not be published. Required fields are marked *