Convert To Numbers from Text

[jazzista]

Hello Message Board: I am trying to convert a small data set from text to number ( AMOUNT Column) . I have tried several methods including text to columns , Fomulas like =VALUE(TRIM(a3)), Trim, etc and I cannot get rid of the trailing spaces to the right. I need a formula solution for this. I know that I can easily resolve this in PQ but I am curious to see why I cannot change the text to numbers. Thanks in advance

Here is the file in the link below.

https://www.dropbox.com/s/snjzd4ua8zkiad7/convert%20to%20numbers.xlsx?dl=0

 

[Michael M]

Try

=LEFT(I2,FIND(" ",I2,1))+0

 

[jazzista]

Mr Michael! Thank you very muchhhh…. My goodness.. I have tried in all sorts of forms to get the text to number. Quick question: why the

FIND(" ",I2,1 Piece...? I guess thats the heart of the formula Thanks Again.... ​

 

[Michael M]

Basically, it is saying
extract all the cells from the LEFT

=LEFT(I2

Until you FIND a space (" ")
and by adding a zero on the end it converts the text to a number

Hope that helps

 

[jazzista]

Mr Michael: Very elegant solution. I got it now. Thank you soooo much again... I will practice this several times...Have a great day...!

 

[Michael M]

Glad to help.....and thx for the feedback...

 

[jazzista]

Mr Michael.. on the contrary... thanks for your help and your quick reply. I was getting so frustared this afternoon since i started doing text to columns and it did not work. Then i tried the trim function and that did not work either..... but now, i learned your trick!

 

[Michael M]

We're always learning...?