Converting Text to Numbers when they're also over 15 characters long

I am afraid versions older that Excel 2019 will require VBA for that.

Here is a handy User defined function that I "borrowed" from Rick Rothstein and modified.

Please Login or Register  to view this content.

If you are not familiar with how to install this:

1. Copy code above
2. Press Alt and F11 on your keyboard to open VB Editor
3. Click on Insert and select Module
4. Paste code into Module and close VB Editor.

Don't forget to save your worbook as Macro-Enabled workbook.

I'm afraid as you have discovered there is a 15 digit limitation for numbers in Excel. They have to be converted to text after that.... with no numeric value

If you are able to upload a sample Excel file (confidential info removed or "dummied" up) there might be a work around. But without seeing what you are actually dealing with it is not easy to tell.

The instructions for file upload are in the gold banner at the top of each page.

I did attach with my original question. Did I not do it correctly? I can see it and click on it?

I need to convert to numbers because I have other tables that have columns that I need to add to this spreadsheet (pricing groups). Both tables have the card numbers as a common column, I need to get the Price groups into a table that also contains the Client ID. My purpose for converting is so I can do Vlookups and Matches. I've tried doing it with them both in "text" and it doesn't work. Nothing but NAs.

S.

Edit: Our posts crossed in the ether. LOL Let me look at your last post.

I'm importing card numbers to excel from crystal reports. The numbers range in character lengths of 2 to 18. It imports all of these cards as a text string.

I am not familiar with crystal reports.

I was researching the 'mid' function to take out some of the middle numbers, but dismissed. I'm preparing our database for a new software and want to keep things as 'pure' as possible.

After reviewing your first and 2nd posts it occurs to me I will need a more thorough understanding of what you are starting with. It appears your Master data already has what you want. What am I missing?

It won't let me do the Vlookup or match functions with them as they are.

It sounds like the lookup issues are what need attention. May we see samples of where lookups are causing problems? There are ways to coerce numbers into text so that they match the text "numbers".

I have seen these type of problems with Vlookup in the past. They were eventually worked out, but without representative context I would have no idea what to suggest. There's almost always a lot of trial and error involved.

OK. Thank you for the latest upload. That helps immensely.

The formula in Worksheet 2 E3 is =VLOOKUP(C3,'Worksheet 1'!A2:C16,3,FALSE)

First the table_array range isn't correct. Recall that the lookup of the lookup_value is performed on the first column of the table_arrary ie column A of 'Worksheet 1'!A2:C16. The card numbers are in column B. Also the table_array range reference needs to be set absolute. The relative range you have changes as you copy down. Those changes made the col_index_num needs to be changed from 3 to 2.

When I do all of that to the formula in column E

=VLOOKUP(C3,'Worksheet 1'!$B$2:$C$16,2,FALSE)

The formula works where there is matching data.

I also tried =VLOOKUP(C3,'Worksheet 1'!$B$2:$B$16,1,FALSE) to test for number vs text issues. I found none. Are you sure this is a representative sample?

Sue you are most welcome. Thank you for the feedback, added rep and marking your thread Solved.

Dave