SOLVED

Quetion related to convert text into number

Iron Contributor

Hello Everyone,

In below images the Sales column is in Text format. So how to convert Text format into Number format with the help of formula?

Screenshot (381).png

6 Replies

@Excel 

If your computer is running an US's system this should do the trick:

=VALUE(B2)

 

If you use other national settings its easiest to use powerQuery (guessing the translation )

Data->fetsh an transform->from table/intervall

DataType->??????

Hmmm, where are the national settings? They used to be there :(

@MindreVetande 

How i will do that with the help of power query? Please explain

best response confirmed by Excel (Iron Contributor)
Solution

@Excel 

select a cell in your "data table" and go to:

Data->Get & Transform->Table/Range

This is from the microsoft page linked below. Bold text = my guessing of relevant settings

  1. Right-click a query column.
  2. Click Change Type > Using Locale.
  3. In the Change Type with Locale dialog box:
  4. To change the destination data type, select a Data Type. (?Accounting?)
    To change the locale, select a Locale. (US.-EN)
  5. Click OK.

If the preview looks ok [Close & load]. 

 

https://support.microsoft.com/en-us/office/internationalization-power-query-d42b9390-1fff-413f-8120-...

Expand: Use a non-default locale setting on a Change Type operation

@Excel Try this:

Select column B

Ctrl-H (Find & Replace)

Find: $

Replace with: leave blank

Press "Replace all"

OK

Close

 

@Riny_van_Eekelen 

Thank you sir so much sir:smiling_face_with_smiling_eyes::smiling_face_with_smiling_eyes:

Thank you so much sir:smiling_face_with_smiling_eyes::smiling_face_with_smiling_eyes:
1 best response

Accepted Solutions
best response confirmed by Excel (Iron Contributor)
Solution

@Excel 

select a cell in your "data table" and go to:

Data->Get & Transform->Table/Range

This is from the microsoft page linked below. Bold text = my guessing of relevant settings

  1. Right-click a query column.
  2. Click Change Type > Using Locale.
  3. In the Change Type with Locale dialog box:
  4. To change the destination data type, select a Data Type. (?Accounting?)
    To change the locale, select a Locale. (US.-EN)
  5. Click OK.

If the preview looks ok [Close & load]. 

 

https://support.microsoft.com/en-us/office/internationalization-power-query-d42b9390-1fff-413f-8120-...

Expand: Use a non-default locale setting on a Change Type operation

View solution in original post