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;
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
edited Jul 13 at 4:58
fixer1234
22.7k15 gold badges59 silver badges91 bronze badges
asked Jul 12 at 19:50
AristotleAristotle
272 bronze badges
add a comment
|
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
edited Jul 13 at 4:58
fixer1234
22.7k15 gold badges59 silver badges91 bronze badges
asked Jul 12 at 19:50
AristotleAristotle
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
add a comment
|
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
edited Jul 13 at 4:58
fixer1234
22.7k15 gold badges59 silver badges91 bronze badges
asked Jul 12 at 19:50
AristotleAristotle
272 bronze badges
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
microsoft-excel
edited Jul 13 at 4:58
fixer1234
22.7k15 gold badges59 silver badges91 bronze badges
asked Jul 12 at 19:50
AristotleAristotle
272 bronze badges
edited Jul 13 at 4:58
fixer1234
22.7k15 gold badges59 silver badges91 bronze badges
asked Jul 12 at 19:50
AristotleAristotle
272 bronze badges
edited Jul 13 at 4:58
fixer1234
22.7k15 gold badges59 silver badges91 bronze badges
edited Jul 13 at 4:58
fixer1234
22.7k15 gold badges59 silver badges91 bronze badges
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
asked Jul 12 at 19:50
AristotleAristotle
272 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
add a comment
|
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
add a comment
|
2 Answers
2
active
oldest
votes
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)
answered Jul 12 at 19:54
Scott CranerScott Craner
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
add a comment
|
You can use this Formula to convert abbreviated data into Numbers:
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),
returns1forK,2forM&3forB.CHOOSEreturns1,000,1,000,000or1,000,000,000which is multiplied byLEFT(A80,LEN(A80)-1).LEFTremoves theKorM.
Adjust cell references in the Formula as needed.
answered Jul 13 at 6:11
Rajesh SRajesh S
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
add a comment
|
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
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
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)
answered Jul 12 at 19:54
Scott CranerScott Craner
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
add a comment
|
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)
answered Jul 12 at 19:54
Scott CranerScott Craner
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
add a comment
|
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)
answered Jul 12 at 19:54
Scott CranerScott Craner
14.5k1 gold badge13 silver badges19 bronze badges
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)
answered Jul 12 at 19:54
Scott CranerScott Craner
14.5k1 gold badge13 silver badges19 bronze badges
answered Jul 12 at 19:54
Scott CranerScott Craner
14.5k1 gold badge13 silver badges19 bronze badges
answered Jul 12 at 19:54
Scott CranerScott Craner
14.5k1 gold badge13 silver badges19 bronze badges
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
add a comment
|
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
add a comment
|
You can use this Formula to convert abbreviated data into Numbers:
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),
returns1forK,2forM&3forB.CHOOSEreturns1,000,1,000,000or1,000,000,000which is multiplied byLEFT(A80,LEN(A80)-1).LEFTremoves theKorM.
Adjust cell references in the Formula as needed.
answered Jul 13 at 6:11
Rajesh SRajesh S
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
add a comment
|
You can use this Formula to convert abbreviated data into Numbers:
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),
returns1forK,2forM&3forB.CHOOSEreturns1,000,1,000,000or1,000,000,000which is multiplied byLEFT(A80,LEN(A80)-1).LEFTremoves theKorM.
Adjust cell references in the Formula as needed.
answered Jul 13 at 6:11
Rajesh SRajesh S
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
add a comment
|
You can use this Formula to convert abbreviated data into Numbers:
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),
returns1forK,2forM&3forB.CHOOSEreturns1,000,1,000,000or1,000,000,000which is multiplied byLEFT(A80,LEN(A80)-1).LEFTremoves theKorM.
Adjust cell references in the Formula as needed.
answered Jul 13 at 6:11
Rajesh SRajesh S
6,2684 gold badges9 silver badges27 bronze badges
You can use this Formula to convert abbreviated data into Numbers:
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),
returns1forK,2forM&3forB.CHOOSEreturns1,000,1,000,000or1,000,000,000which is multiplied byLEFT(A80,LEN(A80)-1).LEFTremoves theKorM.
Adjust cell references in the Formula as needed.
answered Jul 13 at 6:11
Rajesh SRajesh S
6,2684 gold badges9 silver badges27 bronze badges
edited Jul 13 at 6:26
answered Jul 13 at 6:11
Rajesh SRajesh S
6,2684 gold badges9 silver badges27 bronze badges
answered Jul 13 at 6:11
Rajesh SRajesh S
6,2684 gold badges9 silver badges27 bronze badges
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
add a comment
|
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
add a comment
|
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.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
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