| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Big problem.................
I have the following data,
1. 103 2. 1002 3. 205 The last digit of above number represent how many zero it is. 103 equal to 10000 (10K), 1002 equal to 10000 (10k), 205 equal to 2000000(2M) How to use the function to change 103 to 10K, 1002 to 10K, 205 to 2M ? In fact, not only these number , other number I want to change contain ............3491 change to 3.49K, 121 change to 120 ,etc |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Big problem.................
Try: =(LEFT(A1,LEN(A1)-1)&REPT(0,RIGHT(A1)))*1
-- Max Singapore http://savefile.com/projects/236895 Downloads:20,000 Files:362 Subscribers:62 xdemechanik --- "Wu" wrote: I have the following data, 1. 103 2. 1002 3. 205 The last digit of above number represent how many zero it is. 103 equal to 10000 (10K), 1002 equal to 10000 (10k), 205 equal to 2000000(2M) How to use the function to change 103 to 10K, 1002 to 10K, 205 to 2M ? In fact, not only these number , other number I want to change contain ...........3491 change to 3.49K, 121 change to 120 ,etc |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Big problem.................
Hi,
Assuming your first 3 example aare in a1 down put this in b1 and drag down =(A1-RIGHT(A1,1))*10^RIGHT(A1,1) Mike "Wu" wrote: I have the following data, 1. 103 2. 1002 3. 205 The last digit of above number represent how many zero it is. 103 equal to 10000 (10K), 1002 equal to 10000 (10k), 205 equal to 2000000(2M) How to use the function to change 103 to 10K, 1002 to 10K, 205 to 2M ? In fact, not only these number , other number I want to change contain ...........3491 change to 3.49K, 121 change to 120 ,etc |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Big problem.................
=LEFT(A1,LEN(A1)-1)*10^RIGHT(A1,1)
-- Kind regards, Niek Otten Microsoft MVP - Excel "Wu" wrote in message ... I have the following data, 1. 103 2. 1002 3. 205 The last digit of above number represent how many zero it is. 103 equal to 10000 (10K), 1002 equal to 10000 (10k), 205 equal to 2000000(2M) How to use the function to change 103 to 10K, 1002 to 10K, 205 to 2M ? In fact, not only these number , other number I want to change contain ...........3491 change to 3.49K, 121 change to 120 ,etc |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Big problem.................
oops forgot the division
=(A1-RIGHT(A1,1))*10^RIGHT(A1,1)/10 "Mike H" wrote: Hi, Assuming your first 3 example aare in a1 down put this in b1 and drag down =(A1-RIGHT(A1,1))*10^RIGHT(A1,1) Mike "Wu" wrote: I have the following data, 1. 103 2. 1002 3. 205 The last digit of above number represent how many zero it is. 103 equal to 10000 (10K), 1002 equal to 10000 (10k), 205 equal to 2000000(2M) How to use the function to change 103 to 10K, 1002 to 10K, 205 to 2M ? In fact, not only these number , other number I want to change contain ...........3491 change to 3.49K, 121 change to 120 ,etc |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Big problem.................
On Oct 30, 6:11 am, Wu wrote:
The last digit of above number represent how many zero it is. 103 equal to 10000 (10K), 1002 equal to 10000 (10k), 205 equal to 2000000(2M) How to use the function to change 103 to 10K, 1002 to 10K, 205 to 2M ? Assuming you actually want numeric results (e.g. 2000000, not "2M"), try: =INT(A1/10)*10^MOD(A1,10) That also assumes that your original representation (e.g. 103) is numeric. If it is text, change both references to A1 to N(A1). ----- original posting ----- On Oct 30, 6:11*am, Wu wrote: I have the following data, 1. *103 2. *1002 3. *205 The last digit of above number represent how many zero it is. 103 equal to 10000 (10K), 1002 equal to 10000 (10k), 205 equal to 2000000(2M) How to use the function to change 103 to 10K, 1002 to 10K, 205 to 2M ? In fact, not only these number , other number I want to change contain ...........3491 change to 3.49K, 121 change to 120 ,etc |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Big problem.................
YOUR FUNCTION IS WELL, BUT I WOULD LIKE TO CHANGE 102 TO 1K, 103 TO 10K, 205
TO 2M........................... "Niek Otten" wrote: =LEFT(A1,LEN(A1)-1)*10^RIGHT(A1,1) -- Kind regards, Niek Otten Microsoft MVP - Excel "Wu" wrote in message ... I have the following data, 1. 103 2. 1002 3. 205 The last digit of above number represent how many zero it is. 103 equal to 10000 (10K), 1002 equal to 10000 (10k), 205 equal to 2000000(2M) How to use the function to change 103 to 10K, 1002 to 10K, 205 to 2M ? In fact, not only these number , other number I want to change contain ...........3491 change to 3.49K, 121 change to 120 ,etc |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Big problem.................
BUT HOW TO CHANGE 102 TO 1K, 205 TO 2M..................
"Max" wrote: Try: =(LEFT(A1,LEN(A1)-1)&REPT(0,RIGHT(A1)))*1 -- Max Singapore http://savefile.com/projects/236895 Downloads:20,000 Files:362 Subscribers:62 xdemechanik --- "Wu" wrote: I have the following data, 1. 103 2. 1002 3. 205 The last digit of above number represent how many zero it is. 103 equal to 10000 (10K), 1002 equal to 10000 (10k), 205 equal to 2000000(2M) How to use the function to change 103 to 10K, 1002 to 10K, 205 to 2M ? In fact, not only these number , other number I want to change contain ...........3491 change to 3.49K, 121 change to 120 ,etc |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Big problem.................
I WANT TO CHANGE FROME 102 TO 1K, 205 TO 2M.............
"joeu2004" wrote: On Oct 30, 6:11 am, Wu wrote: The last digit of above number represent how many zero it is. 103 equal to 10000 (10K), 1002 equal to 10000 (10k), 205 equal to 2000000(2M) How to use the function to change 103 to 10K, 1002 to 10K, 205 to 2M ? Assuming you actually want numeric results (e.g. 2000000, not "2M"), try: =INT(A1/10)*10^MOD(A1,10) That also assumes that your original representation (e.g. 103) is numeric. If it is text, change both references to A1 to N(A1). ----- original posting ----- On Oct 30, 6:11 am, Wu wrote: I have the following data, 1. 103 2. 1002 3. 205 The last digit of above number represent how many zero it is. 103 equal to 10000 (10K), 1002 equal to 10000 (10k), 205 equal to 2000000(2M) How to use the function to change 103 to 10K, 1002 to 10K, 205 to 2M ? In fact, not only these number , other number I want to change contain ...........3491 change to 3.49K, 121 change to 120 ,etc |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Big problem.................
On Thu, 30 Oct 2008 07:11:01 -0700, Wu wrote:
I have the following data, 1. 103 2. 1002 3. 205 The last digit of above number represent how many zero it is. 103 equal to 10000 (10K), 1002 equal to 10000 (10k), 205 equal to 2000000(2M) How to use the function to change 103 to 10K, 1002 to 10K, 205 to 2M ? In fact, not only these number , other number I want to change contain ...........3491 change to 3.49K, 121 change to 120 ,etc =INT(A1/10)*10^(A1-INT(A1/10)*10) If your values will not exceed something like 2^28, then you could use: =INT(A1/10)*10^MOD(A1,10) --ron |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Started out as an Access problem. Now an Excel problem | Excel Discussion (Misc queries) | |||
| problem with a conditional max problem | Excel Discussion (Misc queries) | |||
Linear Mode
