SOLVED

Quetion related to convert text into number

%3CLINGO-SUB%20id%3D%22lingo-sub-1860702%22%20slang%3D%22en-US%22%3EQuetion%20related%20to%20convert%20text%20into%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1860702%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Everyone%2C%3C%2FP%3E%3CP%3EIn%20below%20images%20the%20Sales%20column%20is%20in%20Text%20format.%20So%20how%20to%20convert%20Text%20format%20into%20Number%20format%20with%20the%20help%20of%20formula%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%20(381).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F231874iB0D7424058BE5AF9%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%20(381).png%22%20alt%3D%22Screenshot%20(381).png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1860702%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1860952%22%20slang%3D%22en-US%22%3ERe%3A%20Quetion%20related%20to%20convert%20text%20into%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1860952%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F811137%22%20target%3D%22_blank%22%3E%40Zan_Hanifee%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20your%20computer%20is%20running%20an%20US's%20system%20this%20should%20do%20the%20trick%3A%3C%2FP%3E%3CP%3E%3DVALUE(B2)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20use%20other%20national%20settings%20its%20easiest%20to%20use%20powerQuery%20(guessing%20the%20translation%20)%3C%2FP%3E%3CP%3EData-%26gt%3Bfetsh%20an%20transform-%26gt%3Bfrom%20table%2Fintervall%3C%2FP%3E%3CP%3EDataType-%26gt%3B%3F%3F%3F%3F%3F%3F%3C%2FP%3E%3CP%3EHmmm%2C%20where%20are%20the%20national%20settings%3F%20They%20used%20to%20be%20there%20%3CLI-EMOJI%20id%3D%22lia_disappointed-face%22%20title%3D%22%3Adisappointed_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1861084%22%20slang%3D%22en-US%22%3ERe%3A%20Quetion%20related%20to%20convert%20text%20into%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1861084%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F857441%22%20target%3D%22_blank%22%3E%40MindreVetande%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20i%20will%20do%20that%20with%20the%20help%20of%20power%20query%3F%20Please%20explain%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1861384%22%20slang%3D%22en-US%22%3ERe%3A%20Quetion%20related%20to%20convert%20text%20into%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1861384%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F811137%22%20target%3D%22_blank%22%3E%40Zan_Hanifee%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eselect%20a%20cell%20in%20your%20%22data%20table%22%20and%20go%20to%3A%3C%2FP%3E%3CP%3EData-%26gt%3BGet%20%26amp%3B%20Transform-%26gt%3BTable%2FRange%3C%2FP%3E%3CP%3E%3CSTRONG%3EThis%20is%20from%20the%20microsoft%20page%20linked%20below.%20Bold%20text%20%3D%20my%20guessing%20of%20relevant%20settings%3C%2FSTRONG%3E%3C%2FP%3E%3COL%3E%3CLI%3ERight-click%20a%20query%20column.%3C%2FLI%3E%3CLI%3EClick%20Change%20Type%20%26gt%3B%20Using%20Locale.%3C%2FLI%3E%3CLI%3EIn%20the%20Change%20Type%20with%20Locale%20dialog%20box%3A%3C%2FLI%3E%3CLI%3ETo%20change%20the%20destination%20data%20type%2C%20select%20a%20Data%20Type.%20%3CEM%3E%3CSTRONG%3E(%3FAccounting%3F)%3C%2FSTRONG%3E%3C%2FEM%3E%3CBR%20%2F%3ETo%20change%20the%20locale%2C%20select%20a%20Locale.%26nbsp%3B%3CEM%3E%3CSTRONG%3E(US.-EN)%3C%2FSTRONG%3E%3C%2FEM%3E%3C%2FLI%3E%3CLI%3EClick%20OK.%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%3CSTRONG%3E%3CEM%3EIf%20the%20preview%20looks%20ok%20%5BClose%20%26amp%3B%20load%5D.%26nbsp%3B%3C%2FEM%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Finternationalization-power-query-d42b9390-1fff-413f-8120-d7df0ced20b9%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Finternationalization-power-query-d42b9390-1fff-413f-8120-d7df0ced20b9%3C%2FA%3E%3C%2FP%3E%3CP%3EExpand%3A%20%3CEM%3EUse%20a%20non-default%20locale%20setting%20on%20a%20Change%20Type%20operation%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional 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
Highlighted

@Zan_Hanifee 

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

Highlighted

@MindreVetande 

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

Highlighted
Best Response confirmed by Zan_Hanifee (Occasional Contributor)
Solution

@Zan_Hanifee 

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

Highlighted

@Zan_Hanifee Try this:

Select column B

Ctrl-H (Find & Replace)

Find: $

Replace with: leave blank

Press "Replace all"

OK

Close

 

Highlighted

@Riny_van_Eekelen 

Thank you sir so much sir

Highlighted
Thank you so much sir