Correct text number to excel number format?

Do two Replace Alls. The first, replace spaces with nothing; the second, replace $ sign with nothing.


Regards, TMS

there are a couple ways. If you want you can use =--A2 and drag down. That'll convert them.
another is to format as number then do a find and replace with one number at a time going from 0 through 9, that will fix them in the same column they are in now.
if you use the first way you can also do a copy and paste special values over the original set and they should now be numbers.

@Sambo kid: =--A2 won't convert them; you'll just get #Value!

Replace 0 through 9? Not sure how that would work

@Sambo kid: =--A2 won't convert them; you'll just get #Value!

Replace 0 through 9? Not sure how that would work

Interesting, it worked on his attached sample.

And the zero through nine replacement, I used to do that a lot when I was a novice. If you highlight the column and do a find and replace where you don't click on match entire cell contents then do find and replace >> find what 0, replace with 0, if they are text you'll see them immediately change to numbers. I've used it when I've had no luck changing formats from text to general to number, if they still won't change then I do that and you can see them change FWIW.

You're welcome.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

@Carla: you're very welcome

This will convert the "numbers" to numbers. Enter in B2 and fill down.

Formula:

Please Login or Register  to view this content.

Another way:
Notice the little green triangles on the upper left of each cell which indicates that there is something to note about the cells. Select all the cells, click on the yellow rectangle and click on Convert to Number.