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

1. ## 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

2. ## 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?

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. ## Coverting numeric text ending in M or K to correct values

Originally Posted by lespaul42
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. 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)

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

#### 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