Text can't convert to values

[Prasenjit Basumatary]

Sir/Madam,

I have attached a Excel workbook. In the sheet1, column A shows numbers but i can't multiply/subtract/sum/divide/add etc. with these numbers etc. I have tried different tricks e.g. Text to Column, Value function , multiply the column A with 1 using the menu paste special, etc but I can't convert. Please help me to convert this numbers to values so that I can do multiply/subtract/sum/divide/add etc.

Thanks in advance.

Regards,
Prasenjit

 

[John Jairo V]

Hi, @Prasenjit Basumatary!

You could try:
=--SUBSTITUTE(A2,",","")

And drag it down. Blessings!

 

[Prasenjit Basumatary]

Yes, It really works.
Thank you for instant support and help.

 

[Prasenjit Basumatary]

Hi, @Prasenjit Basumatary!

You could try:
=--SUBSTITUTE(A2,",","")

And drag it down. Blessings!

Hello @John Jairo V ,

Would you please tell me why did the numbers behave like text.

 

[John Jairo V]

Hi, again!

Would you please tell me why did the numbers behave like text.

In the English version of Excel, the thousands separator is represented by the "," and the decimal separator by "."

For the thousands separator to be recognized by Excel as such when performing a mathematical operation with it being text, this must be every 3 numeric digits from the first whole number. If it is not, it causes error when operating with it. That's why the substitute function is responsible for removing the commas, and then operate it mathematically with the double negative.

I hope you have understood the explanation. Blessings!

 

[Prasenjit Basumatary]

@John Jairo V

Yes, now I have understood.
Thank you once again.