How to MATCH numbers formatted as text in Excel

You will sometimes encounter issues 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 those numbers.

However, if we try to MATCH or LOOKUP those numbers stored and/or formatted as text with actual numbers, we will encounter errors.

Consider the following example:

Given that the number 3 in the B6 cell is stored and formatted as text, we’ve encountered an error when we tried to match it with the number 3 in the G6 cell.

Now consider the following example:

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

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

We have to fill column C with the mail addresses from column F.

I.e., we have to match user numbers from column A with those from column E.

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

In order to successfully match data, we should instead first take the number we are trying to match and format it in the way our data source has formatted it.

In this particular example, we can accomplish that with the help of the TEXT function:

After transforming our lookup value using the TEXT function, i.e., converting a number to text, we have successfully matched our “transformed” lookup value with the values in column E.

Now consider the reverse example:

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

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

We have to fill column C with the mail addresses from column F.

I.e., we have to match 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:

In order to successfully match data, we should instead first take the text we are trying to match and convert that text to a number, given that our data source has user codes formatted as numbers.

Sometimes, we can accomplish that using only the VALUE function:

This example is more complicated.

Had we 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 a number.

There are several ways to do this, the basic one being with the help of the LEFT, MID, and RIGHT functions:

Using the LEFT, MID, and RIGHT functions, we have successfully created a new text string that contains just digits.

Next, we’ve converted that text string to a number with the help of the VALUE function.

Finally, we’ve matched our number with the values in 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: