Convert Text to Number - Large Database

[supplychain.dan]

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!!

[shg]

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

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

[supplychain.dan]

No, there are numbers there like

1234-01-123-1234

like one of the rows has:

1234 01 123 1234 as text

and I need to convert it to it shows as:

1234-01-123-1234 as a number

I'd say there are a 500 mixed over the database

[shg]

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 ####-##-###-####

[supplychain.dan]

Sorry, I'm not sure how that's going to fix it...

[shg]

It corrects your example, Dan.

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