Text to Number

[Coolsandyp]

I am doing text to columns from a text file in excel. It has one column of numbers where numbers are shown as 1,000.00 , 2,500.00, 3,51,000.00......Wherever one comma occurs in numbers, it interprets as number but numbers with 2 commas are interpreted as text. I have tried all methods (Text to column, Value function, multiplication by 1, adding 0,etc ) to convert them back to number but none works. Kindly find me an easy solution.

 

[alansidman]

Bring your table into Power Query, Highlight the column in question. Right click and "Change Type" Select Whole number or Decimal. Save and Load to Excel Native.

 

[Coolsandyp]

Bring your table into Power Query, Highlight the column in question. Right click and "Change Type" Select Whole number or Decimal. Save and Load to Excel Native.

The version I use does not support power query.

 

[rlv01]

... numbers are shown as 1,000.00 , 2,500.00, 3,51,000.00......

Is "3,51,000.00" an example of an instance where the number has two commas? If so, I would expect trouble. Commas need to be in legal positions for numeric values for excel to interpret them as numbers.

 

[Coolsandyp]

Is "3,51,000.00" an example of an instance where the number has two commas? If so, I would expect trouble. Commas need to be in legal positions for numeric values for excel to interpret them as numbers.

Yes....It says the value is Three Lakh Fifty-One Thousand

 

[rlv01]

Sorry to be dense, but what is "Lakh"?

 

[Coolsandyp]

Sorry to be dense, but what is "Lakh"?

In India, 1 lakh means 0.1 million

 

[Akuini]

Why not just remove all comma? you can use SUBSTITUTE function.
so "3,51,000.00" becomes "351000.00"

 

[rlv01]

Ok, I see. Do you have your regional settings in the Win Control panel set to English (India) which seems to be the setting designed to accommodate the unusual (well, at least from my national perspective anyway) comma pattern for Lakh?

 

[Coolsandyp]

Ok, I see. Do you have your regional settings in the Win Control panel set to English (India) which seems to be the setting designed to accommodate the unusual (well, at least from my national perspective anyway) comma pattern for Lakh?

yes