formatting cells for lookups

[Brian.Crawford]

I have a need to match cells (rows) in on table to another (for SUMPRODUCT). The format of the numbers to compare can be like 1234, 1234., 1234.12, 1234.2000, 1234.PHONE. I am trying to coherce everything into the same format so the the comparisons will work. I have tried many different approaches but noting seems to work. Excel makes anything that doesn't have text in it to be a number (even when it says it's formatted as text). I tried CSTR(), appending 0000s and then truncating to 4 digits for those that are less than 4 decimal positing. I've tried formatting the cells as number, text, custom format ####.0000, etc. In addition the 1234 part and the 2nd part are actually two different parts (adjacent cells) being an Acct & Sub-Acct in some sheets and one single code in another (and sometimes 1234.12 shows up as 1234.1200 (which is what it really is)).

These are existing sheets with 1000's of rows so I need a way to do this either in Excel directly or VBA

This has been most frustrating, so if anyone can suggest something I would be most appreciative.
Thanks
Brian

 

[pbornemeier]

Formatting does not change the value of a cells contents.
You can force everything to a text value in a helper column by appends a space to the value from the column of interest.
Format the helper column as General.
If the matching column is A and the Helper column is in H then the formula in row 10 of the helper column (H10) would be:
=A10 & " "

Once the helper column is populated, modify the values in column A as required to get the correct values in the helper column.

After all of these values are satisfactory, convert them to values and perform matches on that column.

 

[Brian.Crawford]

Thanks, I did a variation on this and it seems to be working.