How to convert 1k to 1000 and 1m to 1000000 in ExcelConverting “in place” a column of cells from minutes to hours and minutes in ExcelForcing consistent format for lookup values and lookup arraysExcel Number ScalingHow to convert cells of a column to text in Excel 2007How to embed numeric string in text in Excel?sort numbers and numbers+text together in ExcelHow to combine a range and cell into a new range within a formula in ExcelConvert blank cells in Excel to numericHow to sum a column with cells containing text?Scatter plot chart with positive and negative axis plus label in Excel 2010

How does an ideal op amp amplify a voltage input when the voltage difference is 0?

FIDE Laws of Chess pre-2017

Implement batch option --yes in bash script

How can I say "even today" or "even now"?

Preparing for University Mathematics

AdaFruit ServoKit CircuitPython Blinka Library Installation Problem

Making recursion optional: Bad practice?

Listing files that end in ".txt" from directories with the word "SALARIO" in its name without using pipes

Is the Night's Watch voting system based on history?

In the top five, I'm odd

Can abstractions and good code practice in embedded C++ eliminate the need for the debugger?

JPEG with transparent background

After upgrading to Xcode 11.2 from Xcode 11.1, app crashes due to _UITextLayoutView

What is the difference between the Ancient Greek religion and the Ancient Roman religion?

What does it mean by "commercial support available" for an open-source platform?

Ethics questions concerning a referee assignment

Why is Carbon Dioxide a Greenhouse Gas whereas Ammonia is not?

Replacing 2-prong outlets in basement - existing wiring has two hot wires, one neutral?

Why is it possible to teach real numbers before even rigorously defining them?

Feasibility of keeping an Electrical Bike in poor (wet) storage conditions

How to wire for AC mains voltage relay, when printer board is connected to AC-charging laptop computer?

CircuiTikZ: How to change the node options of labels and annotations?

Can dual US-Canadian citizens travel to the US with an expired US passport but valid Canadian passport?

How to install EdXposed on Android 10 without triggering safety net



How to convert 1k to 1000 and 1m to 1000000 in Excel


Converting “in place” a column of cells from minutes to hours and minutes in ExcelForcing consistent format for lookup values and lookup arraysExcel Number ScalingHow to convert cells of a column to text in Excel 2007How to embed numeric string in text in Excel?sort numbers and numbers+text together in ExcelHow to combine a range and cell into a new range within a formula in ExcelConvert blank cells in Excel to numericHow to sum a column with cells containing text?Scatter plot chart with positive and negative axis plus label in Excel 2010






.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty
margin-bottom:0;









5

















I have text data containing numbers with abbreviations for thousands (k) and millions (m). I want to convert these to numeric values. For example 1.2k to 1200 and 1.8m to 1800000. How can I do that?










share|improve this question























  • 5





    As an aside, a million is typically represented with a capital 'M'. In SI, small 'm' stands for "milli".

    – ivan_pozdeev
    Jul 13 at 18:18


















5

















I have text data containing numbers with abbreviations for thousands (k) and millions (m). I want to convert these to numeric values. For example 1.2k to 1200 and 1.8m to 1800000. How can I do that?










share|improve this question























  • 5





    As an aside, a million is typically represented with a capital 'M'. In SI, small 'm' stands for "milli".

    – ivan_pozdeev
    Jul 13 at 18:18














5












5








5


1






I have text data containing numbers with abbreviations for thousands (k) and millions (m). I want to convert these to numeric values. For example 1.2k to 1200 and 1.8m to 1800000. How can I do that?










share|improve this question

















I have text data containing numbers with abbreviations for thousands (k) and millions (m). I want to convert these to numeric values. For example 1.2k to 1200 and 1.8m to 1800000. How can I do that?







microsoft-excel






share|improve this question
















share|improve this question













share|improve this question




share|improve this question








edited Jul 13 at 4:58









fixer1234

22.7k15 gold badges59 silver badges91 bronze badges




22.7k15 gold badges59 silver badges91 bronze badges










asked Jul 12 at 19:50









AristotleAristotle

272 bronze badges




272 bronze badges










  • 5





    As an aside, a million is typically represented with a capital 'M'. In SI, small 'm' stands for "milli".

    – ivan_pozdeev
    Jul 13 at 18:18













  • 5





    As an aside, a million is typically represented with a capital 'M'. In SI, small 'm' stands for "milli".

    – ivan_pozdeev
    Jul 13 at 18:18








5




5





As an aside, a million is typically represented with a capital 'M'. In SI, small 'm' stands for "milli".

– ivan_pozdeev
Jul 13 at 18:18






As an aside, a million is typically represented with a capital 'M'. In SI, small 'm' stands for "milli".

– ivan_pozdeev
Jul 13 at 18:18











2 Answers
2






active

oldest

votes


















15


















You can use search to return the relative position in a string of the suffix. Then raise ten to that number time 3.



=LEFT(A1,LEN(A1)-1)*10^(SEARCH(RIGHT(A1),"km")*3)


enter image description here






share|improve this answer


























  • Thank Scott Craner

    – Aristotle
    Jul 12 at 20:02











  • @Aristotle please consider marking as correct by clicking the check mark by the answer.

    – Scott Craner
    Jul 12 at 20:10






  • 1





    Note that this will mishandle a number without a suffix.

    – ivan_pozdeev
    Jul 13 at 18:21


















9


















You can use this Formula to convert abbreviated data into Numbers:



enter image description here




  • Formula in Cell C80 & fill it down.



    =IFNA(LEFT(A80,LEN(A80)-1)*CHOOSE(MATCH(RIGHT(A80,1), "K","M","B",0),1000,1000000,1000000000),A80)



N.B.




  • MATCH(RIGHT(A80,1),"K","M","B",0),
    returns 1 for K, 2 for M & 3 for B.


  • CHOOSE returns 1,000, 1,000,000 or 1,000,000,000 which is multiplied by LEFT(A80,LEN(A80)-1).


  • LEFT removes the K or M.

Adjust cell references in the Formula as needed.






share|improve this answer























  • 2





    This formula has the benefit of handling a number without a letter.

    – fixer1234
    Jul 13 at 22:00











  • Thanks @fixer1234,,, ☺

    – Rajesh S
    Jul 14 at 4:37












Your Answer








StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "3"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/4.0/"u003ecc by-sa 4.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);














draft saved

draft discarded
















StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1459247%2fhow-to-convert-1k-to-1000-and-1m-to-1000000-in-excel%23new-answer', 'question_page');

);

Post as a guest















Required, but never shown


























2 Answers
2






active

oldest

votes








2 Answers
2






active

oldest

votes









active

oldest

votes






active

oldest

votes









15


















You can use search to return the relative position in a string of the suffix. Then raise ten to that number time 3.



=LEFT(A1,LEN(A1)-1)*10^(SEARCH(RIGHT(A1),"km")*3)


enter image description here






share|improve this answer


























  • Thank Scott Craner

    – Aristotle
    Jul 12 at 20:02











  • @Aristotle please consider marking as correct by clicking the check mark by the answer.

    – Scott Craner
    Jul 12 at 20:10






  • 1





    Note that this will mishandle a number without a suffix.

    – ivan_pozdeev
    Jul 13 at 18:21















15


















You can use search to return the relative position in a string of the suffix. Then raise ten to that number time 3.



=LEFT(A1,LEN(A1)-1)*10^(SEARCH(RIGHT(A1),"km")*3)


enter image description here






share|improve this answer


























  • Thank Scott Craner

    – Aristotle
    Jul 12 at 20:02











  • @Aristotle please consider marking as correct by clicking the check mark by the answer.

    – Scott Craner
    Jul 12 at 20:10






  • 1





    Note that this will mishandle a number without a suffix.

    – ivan_pozdeev
    Jul 13 at 18:21













15














15










15









You can use search to return the relative position in a string of the suffix. Then raise ten to that number time 3.



=LEFT(A1,LEN(A1)-1)*10^(SEARCH(RIGHT(A1),"km")*3)


enter image description here






share|improve this answer














You can use search to return the relative position in a string of the suffix. Then raise ten to that number time 3.



=LEFT(A1,LEN(A1)-1)*10^(SEARCH(RIGHT(A1),"km")*3)


enter image description here







share|improve this answer













share|improve this answer




share|improve this answer










answered Jul 12 at 19:54









Scott CranerScott Craner

14.5k1 gold badge13 silver badges19 bronze badges




14.5k1 gold badge13 silver badges19 bronze badges















  • Thank Scott Craner

    – Aristotle
    Jul 12 at 20:02











  • @Aristotle please consider marking as correct by clicking the check mark by the answer.

    – Scott Craner
    Jul 12 at 20:10






  • 1





    Note that this will mishandle a number without a suffix.

    – ivan_pozdeev
    Jul 13 at 18:21

















  • Thank Scott Craner

    – Aristotle
    Jul 12 at 20:02











  • @Aristotle please consider marking as correct by clicking the check mark by the answer.

    – Scott Craner
    Jul 12 at 20:10






  • 1





    Note that this will mishandle a number without a suffix.

    – ivan_pozdeev
    Jul 13 at 18:21
















Thank Scott Craner

– Aristotle
Jul 12 at 20:02





Thank Scott Craner

– Aristotle
Jul 12 at 20:02













@Aristotle please consider marking as correct by clicking the check mark by the answer.

– Scott Craner
Jul 12 at 20:10





@Aristotle please consider marking as correct by clicking the check mark by the answer.

– Scott Craner
Jul 12 at 20:10




1




1





Note that this will mishandle a number without a suffix.

– ivan_pozdeev
Jul 13 at 18:21





Note that this will mishandle a number without a suffix.

– ivan_pozdeev
Jul 13 at 18:21













9


















You can use this Formula to convert abbreviated data into Numbers:



enter image description here




  • Formula in Cell C80 & fill it down.



    =IFNA(LEFT(A80,LEN(A80)-1)*CHOOSE(MATCH(RIGHT(A80,1), "K","M","B",0),1000,1000000,1000000000),A80)



N.B.




  • MATCH(RIGHT(A80,1),"K","M","B",0),
    returns 1 for K, 2 for M & 3 for B.


  • CHOOSE returns 1,000, 1,000,000 or 1,000,000,000 which is multiplied by LEFT(A80,LEN(A80)-1).


  • LEFT removes the K or M.

Adjust cell references in the Formula as needed.






share|improve this answer























  • 2





    This formula has the benefit of handling a number without a letter.

    – fixer1234
    Jul 13 at 22:00











  • Thanks @fixer1234,,, ☺

    – Rajesh S
    Jul 14 at 4:37















9


















You can use this Formula to convert abbreviated data into Numbers:



enter image description here




  • Formula in Cell C80 & fill it down.



    =IFNA(LEFT(A80,LEN(A80)-1)*CHOOSE(MATCH(RIGHT(A80,1), "K","M","B",0),1000,1000000,1000000000),A80)



N.B.




  • MATCH(RIGHT(A80,1),"K","M","B",0),
    returns 1 for K, 2 for M & 3 for B.


  • CHOOSE returns 1,000, 1,000,000 or 1,000,000,000 which is multiplied by LEFT(A80,LEN(A80)-1).


  • LEFT removes the K or M.

Adjust cell references in the Formula as needed.






share|improve this answer























  • 2





    This formula has the benefit of handling a number without a letter.

    – fixer1234
    Jul 13 at 22:00











  • Thanks @fixer1234,,, ☺

    – Rajesh S
    Jul 14 at 4:37













9














9










9









You can use this Formula to convert abbreviated data into Numbers:



enter image description here




  • Formula in Cell C80 & fill it down.



    =IFNA(LEFT(A80,LEN(A80)-1)*CHOOSE(MATCH(RIGHT(A80,1), "K","M","B",0),1000,1000000,1000000000),A80)



N.B.




  • MATCH(RIGHT(A80,1),"K","M","B",0),
    returns 1 for K, 2 for M & 3 for B.


  • CHOOSE returns 1,000, 1,000,000 or 1,000,000,000 which is multiplied by LEFT(A80,LEN(A80)-1).


  • LEFT removes the K or M.

Adjust cell references in the Formula as needed.






share|improve this answer
















You can use this Formula to convert abbreviated data into Numbers:



enter image description here




  • Formula in Cell C80 & fill it down.



    =IFNA(LEFT(A80,LEN(A80)-1)*CHOOSE(MATCH(RIGHT(A80,1), "K","M","B",0),1000,1000000,1000000000),A80)



N.B.




  • MATCH(RIGHT(A80,1),"K","M","B",0),
    returns 1 for K, 2 for M & 3 for B.


  • CHOOSE returns 1,000, 1,000,000 or 1,000,000,000 which is multiplied by LEFT(A80,LEN(A80)-1).


  • LEFT removes the K or M.

Adjust cell references in the Formula as needed.







share|improve this answer















share|improve this answer




share|improve this answer








edited Jul 13 at 6:26

























answered Jul 13 at 6:11









Rajesh SRajesh S

6,2684 gold badges9 silver badges27 bronze badges




6,2684 gold badges9 silver badges27 bronze badges










  • 2





    This formula has the benefit of handling a number without a letter.

    – fixer1234
    Jul 13 at 22:00











  • Thanks @fixer1234,,, ☺

    – Rajesh S
    Jul 14 at 4:37












  • 2





    This formula has the benefit of handling a number without a letter.

    – fixer1234
    Jul 13 at 22:00











  • Thanks @fixer1234,,, ☺

    – Rajesh S
    Jul 14 at 4:37







2




2





This formula has the benefit of handling a number without a letter.

– fixer1234
Jul 13 at 22:00





This formula has the benefit of handling a number without a letter.

– fixer1234
Jul 13 at 22:00













Thanks @fixer1234,,, ☺

– Rajesh S
Jul 14 at 4:37





Thanks @fixer1234,,, ☺

– Rajesh S
Jul 14 at 4:37


















draft saved

draft discarded















































Thanks for contributing an answer to Super User!


  • Please be sure to answer the question. Provide details and share your research!

But avoid


  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1459247%2fhow-to-convert-1k-to-1000-and-1m-to-1000000-in-excel%23new-answer', 'question_page');

);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown









Popular posts from this blog

Tamil (spriik) Luke uk diar | Nawigatjuun

Align equal signs while including text over equalitiesAMS align: left aligned text/math plus multicolumn alignmentMultiple alignmentsAligning equations in multiple placesNumbering and aligning an equation with multiple columnsHow to align one equation with another multline equationUsing \ in environments inside the begintabularxNumber equations and preserving alignment of equal signsHow can I align equations to the left and to the right?Double equation alignment problem within align enviromentAligned within align: Why are they right-aligned?

Training a classifier when some of the features are unknownWhy does Gradient Boosting regression predict negative values when there are no negative y-values in my training set?How to improve an existing (trained) classifier?What is effect when I set up some self defined predisctor variables?Why Matlab neural network classification returns decimal values on prediction dataset?Fitting and transforming text data in training, testing, and validation setsHow to quantify the performance of the classifier (multi-class SVM) using the test data?How do I control for some patients providing multiple samples in my training data?Training and Test setTraining a convolutional neural network for image denoising in MatlabShouldn't an autoencoder with #(neurons in hidden layer) = #(neurons in input layer) be “perfect”?