How to get millions from M in Excel?

November 29, 2012 4:43 AM

Can Excel do input filtering or parsing of unit abbreviations? I want to be able to type "400M" and have the result be a number cell of 400000000, not the string "400M"
posted by fightorflight to computers & internet (10 answers total)

You can input scientific notation. Excel interprets 4e6 as 4000000. You will have to change the display if you don't want it to display as 4e6 though.
posted by rockindata at 4:47 AM on November 29, 2012

Rockindata's answer is best.

Strictly in the interest of providing an alternative, you could add a few defined names where "M=1000000", "k=1000", "c=100", etc. Then, you input your formula as "=400*M" and you'll get the result you want, but that's two extra characters.

So rockindata's answer is still best.
posted by Mons Veneris at 5:12 AM on November 29, 2012

If you're doing a large amount of data entry, you can also do a find and replace at the end and change all the Ms to 000000, for example.
posted by something something at 5:18 AM on November 29, 2012 [2 favorites]

In column B put the formula =RIGHT(A1,1) assuming A1 is the field where you enter data.
Then in column C =VLOOKUP(B1,LookupTable,2,0) and then define a named range called LookupTable with two columns: on the left K, M, etc. and on the right 1000, 1000000, and so on. Then in column D, =LEFT(A1,LEN(A1)-1)*C1 and that will be the desired result. Obviously you can make this all one formula instead of using 3 extra columns but I just wanted to break it out step by step. You might need to tweak it as I am writing this in bed while half asleep but you get the idea.
posted by pravit at 5:34 AM on November 29, 2012

I do reporting in Thousands, and you can format the cells to do this automatically.

Format Cells, Number, Custom, then scroll down. This is what my custom number looks like:

($#,##0,_);[Red]($#,##0,)

So when I put in a number in millions, it rounds up and shows it in Thousands:

$5,500,353 will show up in my report as $5,500

The thing with [Red] means that negative numbers show up as red.

This is great, especially if you're importing a ton of data, just update the columns et voila!
posted by Ruthless Bunny at 5:50 AM on November 29, 2012

You could add an Autocorrect entry to change 0M to 000000.
posted by beyond_pink at 5:54 AM on November 29, 2012

Be careful setting up any of these suggestions to define M as 000000; it might work if you're in a numbers-only environment, but if your columns are labeled with things like "Monday", "123 Main St", or any words involving the letter M, things could get messy.
posted by aimedwander at 6:50 AM on November 29, 2012 [1 favorite]

Be careful setting up any of these suggestions to define M as 000000

Yeah, this is a good point. Rather than selecting the entire sheet and formatting as Ruthless Bunny suggests, select a given range of cells and format those cells only with the number format you want.

If it were me, though, I would use scientific notation.
posted by dfriedman at 6:52 AM on November 29, 2012

You could do this in the opposite direction by having the following input mask:

[>=1000000] #,##0,,"M";General

I wonder if an input mask could accomplish what you're looking for...or if it could be done via VBA validation?
posted by samsara at 7:00 AM on November 29, 2012

It may be slight overkill, but if you're doing this a lot, the following function will check and update cells after they've been edited:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Right(Target.Value, 1) = UCase("M") And IsNumeric(Left(Target.Value, Len(Target.Value) - 1)) Then
Target.Value = Left(Target.Value, Len(Target.Value) - 1) * 1000000
End If
On Error GoTo 0
End Sub

posted by pompomtom at 3:15 PM on November 29, 2012 [1 favorite]