Convert a number like 2.2B into 2,200,000,000 in Apple Numbers?

Here's one way, using a lookup table and VLOOKUP.

Table 2, on the right, contains the single letter symbols for Billion, Million and Thousand, and the formula crearting the corresponding values in column B, shown below Table 2 in the for it takes in cell B2.

In B3, the 9 is replaced with 6, and in B4, with 3.

Table 1, on the left, contains the longer formula below both tables, shown as entered in cell A2 of Table 1, then filled down to A5.

Row 6 of this table is left empty as I had'nt added a 'switch' to prevet calculating until a value was entered in A6.

What it does:

VALUE(LEFT(A2,LEN(A2)−1))×
VALUE(VLOOKUP(RIGHT(A2,1),Table 2::A:B,2,0))

VALUE(LEFT(A2,LEN(A2)-1))

LEN(A2) returns the LENgth of the text value in A2 in characters (4)

-1 subtracts 1 from that value. (3)

LEFT(A2,3) gets the leftmost 3 characters of the text in A2 ( 2.2 )

VALUE returns the numeric value represented by those text characters (two and two tenths)

is the multiplication sign. When entering a formula, use the multiplication operator ( * ). Numbers will immediately change

it to the muliplication sign, but will not accept or recognize the multiplication sign entered directly into a formula.


VALUE(VLOOKUP(RIGHT(A2,1),Table 2::A:B,2,0))

RIGHT(A2,1) gets the last character fro the text value in A2, and hands it to VLOOKUP.

VLOOKUP searches for that character in column A of TA=able 2::A:B, finds it,, returns the value from the same row of the second column of the lookup table (Table 2), and hands that to VALUE

× multiplies the Value to its left by the VALUE to its right, and returns the result to its cell, B2 of Table 1.

Regards,

Barry

I would have thought there would be some tricky way other than functions but I haven't found it. Just for fun I did it with a different formula using some of the newer funtions.

If the numbers are in column A,

=IFERROR(REGEX.EXTRACT(A,"[0-9.,]+"),0)*10^XLOOKUP(RIGHT(A,1),{"T","B","M","K"},{12,9,6,3},0,0)

This handles numbers ending in T B M or K and just plain numbers. The number can contain a decimal point and comma separators. The result will be zero if there is no number. If used on text that just happens to contain a number, it will give a result based on the first number found.

I am not sure if the use of curly brackets to create an array in a function is documented or not but it has worked since at least Numbers '09.

You can also do this in one cell with basic functions, like this:

With the value A3, the formula would be:

=LEFT(A3,LEN(A3)−1)*1000^FIND(RIGHT(A3,1),"KMBT")

If you are using different abbreviations than "K" for thousands, "M" for millions, "B" for billions, "T" for trillions, then just replace the "KMBT" with the ones you are using, in ascending order.

SG

SG's formula seems the most elegant. I modified it to accept numbers that have no modifying letter:

=IFERROR(LEFT(A3,LEN(A3)−1)×1000^FIND(RIGHT(A3,1),"KMBT",1),A3)

Empty cell = 0

Text or non-numeric = the same text or non-numeric

Number with no modifying letter = the number

Number with modifying letter = number x the correct power of 1000

As an aside, you can create a custom format that will append the correct K, M, B, or T to a number. For example, 1000 would display as 1K in the cell. Unfortunately it does not appear to let you enter a number as 1K, you have to enter it as 1000.

Badunit wrote: "SG's formula seems the most elegant. "

Agreed. Your modification adds a nice touch as well.

Regards,

Barry