Convert Text to Number - Large Database

Hello All,

I need help! I've researched everyway to convert text t numbers. I'm running a database of inventory numbers. The format of the numbers are:

####-##-###-####

I have 2000+ numbers is coloumn B.

There are a lot of sets of numbers that are still text! It's highly annoying and I need to analyze the data in Access.

I tried the paste special advice and the 3 ways microsoft suggests. I have a feeling its not working because of the required format that I need.

I need to do this project tonight!

Background: these are imported numbers, I had to combine other excel databases... By the way, is their an easier way to combine excel sheets into 1?


THANKS SO MUCH!!

Assuming you want the numeric value of the number sans dashes,

=substitute(a1, "-", "") + 0

1234 01 123 1234 as text

and I need to convert it to it shows as:

1234-01-123-1234 as a number

=substitute(a1, " ", "") + 0 and format as ####-##-###-####

It corrects your example, Dan.

If your example doesn't encompass the problem, post a workbook.

If I understand you question, copy the column with the formulas, and paste special over itself as values, then delete the original column.

Or, copy the column with the formulas, and paste special over the original data as values, then delete the formula column.