# Very similar column - TEXT Filter on one sheet, NUMBER Filter on other - Why different?

1. ## Very similar column - TEXT Filter on one sheet, NUMBER Filter on other - Why different?

I have a workbook with two sheets. On sheet one is a single column with a header and numbers below. On the second sheet is a very similar column and some other data in the sheet. My question is about Column A in both sheets. It was pointed out to me that Column A in sheet 1 is formatted differently than Column A in sheet 2.

Upon clicking Format Cells for each column, they indicate that they are "Number" and "General". However, when a filter is added to the columns, Column A on sheet 1 filters as "Numbers". Column A on sheet 2 filters as "Text". This was not done intentionally. Why would this happen?

I need to use a formula to combine and align the sheets based on information in Column A of both sheets and apparently this is messing me up. How can I make both columns to be formatted the same?

2. ## Re: Very similar column - TEXT Filter on one sheet, NUMBER Filter on other - Why different

Where did the numbers that are text come from? You can work round the mistmatch via formulae, so no need to worry too much about it, although it would be useful to know where the text came from. Is that list the result of a formula?

3. ## Re: Very similar column - TEXT Filter on one sheet, NUMBER Filter on other - Why different

Hello AliGW.

The column in sheet 1 was made by typing the first six digit "code" number and then dragging down to the logical end amount of six digit numbers, within a structure of being restrained by the first two numbers (710000 - 719999).

The information on sheet 2 was produced (Excel report) by the program used to track this information. As you can see, the numbers in Column A in sheet 2, have breaks between the numbers. That is the difference between Column A on both sheets. That is my main purposed in this, is listing every possible number in Column A on sheet 1 and trying to bring over all the data on sheet 2, based on how they align by matching the "code" numbers on sheet 1.

For disclosure (not breaking forum rules), I already have received help on this great forum in achieving the desired end result. But, in doing so, caused me to ask the present question of the difference in filtering. I am posting the end result spreadsheet just so you can see what I am talking about. But the formula that was very generously offered to me, had a component that I did not understand (the formula negates the filtering difference).

4. ## Re: Very similar column - TEXT Filter on one sheet, NUMBER Filter on other - Why different

Sheet 1

Maybe like this.

F2 =IF(B2="","","filter") and drag down.

After that filter on column F and choose for "filter"

See the attached file.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1