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.

 

[rlv01]

Out of curiosity, I set my PC regional settings to English (India) and used text to columns on 3 columns of data as shown below pasted in from a text file:

Data Col1 Data Col2 Data Col3
1,000.50 2,250.50 5,550.50
10,000.50 20,250.50 50,550.50
1,00,000.50 2,00,250.50 5,00,550.50
10,00,000.50 20,00,250.50 50,00,550.50
1,00,00,000.50 2,00,00,250.50 5,00,00,550.50
10,00,00,000.50 20,00,00,250.50 50,00,00,550.50
1,00,00,00,000.50 2,00,00,00,250.50 5,00,00,00,550.50
10,00,00,00,000.50 20,00,00,00,250.50 50,00,00,00,550.50

The text to column function correctly interpreted all of them as numeric values:

Data Col1	Data Col2	Data Col3
1,000.50	2,250.50	5,550.50
10,000.50	20,250.50	50,550.50
1,00,000.50	2,00,250.50	5,00,550.50
10,00,000.50	20,00,250.50	50,00,550.50
1,00,00,000.50	2,00,00,250.50	5,00,00,550.50
10,00,00,000.50	20,00,00,250.50	50,00,00,550.50
1,00,00,00,000.50	2,00,00,00,250.50	5,00,00,00,550.50
10,00,00,00,000.50	20,00,00,00,250.50	50,00,00,00,550.50

When I changed my regional setting back to English (United States) and repeated the text to column exercise on the same data. Only the values with one comma were interpreted as numbers. Any with 2 or more commas were interpreted as text. Just as you described your problem. So the text to column feature seems to work for the two column digital grouping, so long as the regional setting is correct.

 

[Coolsandyp]

Out of curiosity, I set my PC regional settings to English (India) and used text to columns on 3 columns of data as shown below pasted in from a text file:

Data Col1 Data Col2 Data Col3
1,000.50 2,250.50 5,550.50
10,000.50 20,250.50 50,550.50
1,00,000.50 2,00,250.50 5,00,550.50
10,00,000.50 20,00,250.50 50,00,550.50
1,00,00,000.50 2,00,00,250.50 5,00,00,550.50
10,00,00,000.50 20,00,00,250.50 50,00,00,550.50
1,00,00,00,000.50 2,00,00,00,250.50 5,00,00,00,550.50
10,00,00,00,000.50 20,00,00,00,250.50 50,00,00,00,550.50

The text to column function correctly interpreted all of them as numeric values:

Data Col1	Data Col2	Data Col3
1,000.50	2,250.50	5,550.50
10,000.50	20,250.50	50,550.50
1,00,000.50	2,00,250.50	5,00,550.50
10,00,000.50	20,00,250.50	50,00,550.50
1,00,00,000.50	2,00,00,250.50	5,00,00,550.50
10,00,00,000.50	20,00,00,250.50	50,00,00,550.50
1,00,00,00,000.50	2,00,00,00,250.50	5,00,00,00,550.50
10,00,00,00,000.50	20,00,00,00,250.50	50,00,00,00,550.50

When I changed my regional setting back to English (United States) and repeated the text to column exercise on the same data. Only the values with one comma were interpreted as numbers. Any with 2 or more commas were interpreted as text. Just as you described your problem. So the text to column feature seems to work for the two column digital grouping, so long as the regional setting is correct.

Thank You. I will try the same. Thank you very much

 

[alansidman]

The version I use does not support power query.

This statement is not true. Power Query is called Get and Transform and is native to Excel2016 and Excel 2019 which you have indicated you are using. It can be downloaded from Microsoft for your 2013 version.