+ Reply to Thread
Results 1 to 5 of 5

converting 10.27M to 10,270,000...

  1. #1
    Registered User
    Join Date
    01-12-2009
    Location
    Ithaca, NY
    MS-Off Ver
    Excel 2004 Mac
    Posts
    3

    converting 10.27M to 10,270,000...

    I'm having trouble fixing this problem. The issue is that there are six types of "numbers" in the table that I am trying to turn into all numbers. Obviously, I'd like to turn the Ks in 1,000s and the Ms into 1,000,000s:

    #.##K
    #.#K
    #K
    #.##M
    #.#M
    #M

    If there weren't all six, I think I could fix this pretty easily, but as it is I'm pretty stumped (other than a manual fix). Any thoughts would be appreciated.

    Sam
    Last edited by lespaul42; 01-12-2009 at 06:42 PM.

  2. #2
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Coverting numeric text ending in M or K to correct values

    Maybe something like this?....

    With A1:A6 containing these values
    10.27M
    10.2M
    10M
    5.55K
    5.5K
    5K

    If A1 ends with either "M" or "K", this formula converts A1's text to the
    correct number, otherwise it returns the A1 text value

    Please Login or Register  to view this content.
    Copy that formula down through B6.
    In the above example, the formula return these values:

    Please Login or Register  to view this content.
    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Registered User
    Join Date
    01-12-2009
    Location
    Ithaca, NY
    MS-Off Ver
    Excel 2004 Mac
    Posts
    3
    wow. i don't know how easy that was for you, but it looks like it works and it is going to be a huge time saver. i never would have figured all that out.

    thanks so much

  4. #4
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Coverting numeric text ending in M or K to correct values

    Quote Originally Posted by lespaul42 View Post
    wow. i don't know how easy that was for you, but it looks like it works and it is going to be a huge time saver. i never would have figured all that out.
    Well....maybe this variation is a bit easier to follow
    (it's a bit shorter, too):

    Please Login or Register  to view this content.

  5. #5
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    or if they always only end in K or M
    =IF(RIGHT(A1,1)="m",LEFT(A1,LEN(A1)-1)*10^6,LEFT(A1,LEN(A1)-1)*10^3)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1