convert numbers with K and M
 

I have a column of text that represent numbers which have been abbreviated
with K (for thousands) and M (for millions). (e.g. 123400 is displayed as
123.4K, 123456000 is displayed as 123.456M) How can I convert these to
numbers?

Answer: convert numbers with K and M
 

To convert abbreviated numbers to actual numbers in Excel:

1. Select the column of abbreviated numbers that you want to convert.
2. Click on the Home tab in the ribbon.
3. Click on the Find & Select button and select Replace from the dropdown menu.
4. In the Find what field, type "
   Code:

   ---

   K

   ---

   " (without the quotes).
5. In the Replace with field, type "
   Code:

   ---

   *1000

   ---

   " (without the quotes).
6. Click on the Replace All button.
7. Repeat steps 4-6, but this time replace "
   Code:

   ---

   M

   ---

   " with "
   Code:

   ---

   *1000000

   ---

   ".
8. Now, you should have a column of numbers that are still in text format, but with the appropriate number of zeros added to the end.
9. To convert these to actual numbers, use the following formula:
   Code:

   ---

   =LEFT(A1,LEN(A1)-1)*RIGHT(A1,1)

   ---

   (Note: Replace "A1" with the cell reference of the first cell in your column.)
10. Copy the formula down to the rest of the cells in the column.
11. Finally, select the entire column and click on the Number Format dropdown in the ribbon. Select Number to format the cells as numbers.

And that's it! Your column of abbreviated numbers should now be converted to actual numbers in Excel. Let me know if you have any questions or if there's anything else I can help you with.

convert numbers with K and M
 

I would do a few Edit|Replaces.

Select the range to fix
edit|Replace
what: . (decimal point)
with: (leave blank)
replace all

edit|Replace
what: K
with: 000
replace all

edit|Replace
what: M
with: 000000
replace all

Then format them the way I want.


bbs wrote:

I have a column of text that represent numbers which have been abbreviated
with K (for thousands) and M (for millions). (e.g. 123400 is displayed as
123.4K, 123456000 is displayed as 123.456M) How can I convert these to
numbers?

--

Dave Peterson

convert numbers with K and M
 

hi,
the mid function might work.
for your first example....=MID(E8,1,5)*1000
for your second example....=MID(E9,1,7)*1000000
cells E8 and E9 in the formulas above would contain the values you wish to
convert.
read up on the mid function for more info.
yes i know. might be a little work but there isn't any consistency.

regards
FSt1

"bbs" wrote:

I have a column of text that represent numbers which have been abbreviated
with K (for thousands) and M (for millions). (e.g. 123400 is displayed as
123.4K, 123456000 is displayed as 123.456M) How can I convert these to
numbers?

convert numbers with K and M
 

Try the following:

=left(a1,len(a1)-1)*if(right(a1,1)="K",1000,if(right(a1,1)="M",1000 000,1))

--
Regards,
Fred


"bbs" wrote in message
...
I have a column of text that represent numbers which have been abbreviated
with K (for thousands) and M (for millions). (e.g. 123400 is displayed as
123.4K, 123456000 is displayed as 123.456M) How can I convert these to
numbers?

convert numbers with K and M
 

This works fine if there are no decimals, but would change 123.4K to 1234000,
not 123400.

--
Regards,
Fred


"Dave Peterson" wrote in message
...
I would do a few Edit|Replaces.

Select the range to fix
edit|Replace
what: . (decimal point)
with: (leave blank)
replace all

edit|Replace
what: K
with: 000
replace all

edit|Replace
what: M
with: 000000
replace all

Then format them the way I want.


bbs wrote:

I have a column of text that represent numbers which have been abbreviated
with K (for thousands) and M (for millions). (e.g. 123400 is displayed as
123.4K, 123456000 is displayed as 123.456M) How can I convert these to
numbers?

--

Dave Peterson

convert numbers with K and M
 

On Tue, 8 May 2007 21:33:40 -0600, "Fred Smith" wrote:

Try the following:

=left(a1,len(a1)-1)*if(right(a1,1)="K",1000,if(right(a1,1)="M",1000 000,1))

This formula assumes that every number has either a K or M suffix. If there
are values that might be entered with no suffix, then:

=IF(ISNUMBER(-A1),--A1,LEFT(A1,LEN(A1)-1)*
IF(RIGHT(A1,1)="K",1000,IF(RIGHT(A1,1)="M",1000000 ,1)))

--ron

convert numbers with K and M
 

You're right.

Sorry.

Fred Smith wrote:

This works fine if there are no decimals, but would change 123.4K to 1234000,
not 123400.

--
Regards,
Fred

"Dave Peterson" wrote in message
...
I would do a few Edit|Replaces.

Select the range to fix
edit|Replace
what: . (decimal point)
with: (leave blank)
replace all

edit|Replace
what: K
with: 000
replace all

edit|Replace
what: M
with: 000000
replace all

Then format them the way I want.


bbs wrote:

I have a column of text that represent numbers which have been abbreviated
with K (for thousands) and M (for millions). (e.g. 123400 is displayed as
123.4K, 123456000 is displayed as 123.456M) How can I convert these to
numbers?

--

Dave Peterson

--

Dave Peterson

convert numbers with K and M
 

Actually, if all the numbers included a decimal point and a tenths digit, then I
just screwed up my number of 0's.

123000 would have to be displayed as 123.0K for this to work:


Select the range to fix
edit|Replace
what: . (decimal point)
with: (leave blank)
replace all

edit|Replace
what: K
with: 00
replace all

edit|Replace
what: M
with: 00000
replace all

Fred Smith wrote:

This works fine if there are no decimals, but would change 123.4K to 1234000,
not 123400.

--
Regards,
Fred

"Dave Peterson" wrote in message
...
I would do a few Edit|Replaces.

Select the range to fix
edit|Replace
what: . (decimal point)
with: (leave blank)
replace all

edit|Replace
what: K
with: 000
replace all

edit|Replace
what: M
with: 000000
replace all

Then format them the way I want.


bbs wrote:

I have a column of text that represent numbers which have been abbreviated
with K (for thousands) and M (for millions). (e.g. 123400 is displayed as
123.4K, 123456000 is displayed as 123.456M) How can I convert these to
numbers?

--

Dave Peterson

--

Dave Peterson