Text to Number

Coolsandyp

New Member
Joined
Mar 29, 2020
Messages
7
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
  2. Mobile
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.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,656
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 29, 2020
Messages
7
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
  2. Mobile
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

Well-known Member
Joined
May 16, 2017
Messages
1,136
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
... 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

New Member
Joined
Mar 29, 2020
Messages
7
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

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

Well-known Member
Joined
May 16, 2017
Messages
1,136
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Sorry to be dense, but what is "Lakh"?
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,549
Office Version
  1. 365
Platform
  1. Windows
Why not just remove all comma? you can use SUBSTITUTE function.
so "3,51,000.00" becomes "351000.00"
 

rlv01

Well-known Member
Joined
May 16, 2017
Messages
1,136
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Mar 29, 2020
Messages
7
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
  2. Mobile
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
 

Forum statistics

Threads
1,147,451
Messages
5,741,195
Members
423,647
Latest member
lyanndominique

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top