|
Change in Cell Value
Hi All,
I have a excel sheet contains different values in following format: Col. A 0101 0101 10 0101 10 10 0101 10 20 0101 10 30 and so on. Results need in two separate formats in following manner: 1. Col. B 01.01 0101.10 010110.10 010110.20 010110.30 2. Col. C 0101 010110 01011010 01011020 01011030 How can I do this with a code? Thanks in advance. |
Change in Cell Value
You'll have to play with it to get exactly what you're looking for,
but something like this will work. Example: 0101 10 20 =CONCATENATE(LEFT(A24,4),".",MID(A24,6,2),".",RIGH T(A24,2)) will output 0101.10.20 |
Change in Cell Value
1) =LEFT(A1,LEN(A1)-2)&"."&RIGHT(A1,2)
2) =SUBSTITUTE(A1," ","") Copy down in each case "ng6971" wrote: Hi All, I have a excel sheet contains different values in following format: Col. A 0101 0101 10 0101 10 10 0101 10 20 0101 10 30 and so on. Results need in two separate formats in following manner: 1. Col. B 01.01 0101.10 010110.10 010110.20 010110.30 2. Col. C 0101 010110 01011010 01011020 01011030 How can I do this with a code? Thanks in advance. |
Change in Cell Value
Hello JMay,
Thanks for quick response. After your suggestion the results a 1. Col. B 1.01 (Result need: 01.01) 0101 .10 (Result need: 0101.10) remove all spaces (figures not changed) 0101 10 .10 (Result need: 010110.10) same as above 0101 10 .20 (Result need: 010110.20) same as above 0101 10 .30 (Result need: 010110.20) same as above 2. Col. C 101 (Result need: 0101) 010110 (Result OK) 01011010 (Result OK) 01011020 (Result OK) 01011030 (Result OK) When I paste the formula in cells the figures changes as shown above. Figures should not be change in any manner. Once again Thank you very much. "JMay" wrote: 1) =LEFT(A1,LEN(A1)-2)&"."&RIGHT(A1,2) 2) =SUBSTITUTE(A1," ","") Copy down in each case "ng6971" wrote: Hi All, I have a excel sheet contains different values in following format: Col. A 0101 0101 10 0101 10 10 0101 10 20 0101 10 30 and so on. Results need in two separate formats in following manner: 1. Col. B 01.01 0101.10 010110.10 010110.20 010110.30 2. Col. C 0101 010110 01011010 01011020 01011030 How can I do this with a code? Thanks in advance. |
Change in Cell Value
Hello JMay,
Let me explain you in more details. I have a list which has 1. 0101 (4 digit value) 2. 0101 00 (6 digit value) 3. 0101 00 10 (8 digit value) What I want to do for 1st criteria: 1. Where as 4 digit value in column put decimal after 2 digits. 2. Where as 6 digit value [there is a space after 4 digits] remove the space and put decimal after 4 digits. 3. Where as 8 digit value [remove space after 4 and 6 digits and put decimal after 6 digits. In 2nd crieteria: 1. Where as 4 digit value in column the value should be 0000. 2. Where as 6 digit value in column the value should be 000000. 3. Where as 8 digit value in column the value should be 00000000. The results of the values should not changed in any manner. Thanks. "ng6971" wrote: Hello JMay, Thanks for quick response. After your suggestion the results a 1. Col. B 1.01 (Result need: 01.01) 0101 .10 (Result need: 0101.10) remove all spaces (figures not changed) 0101 10 .10 (Result need: 010110.10) same as above 0101 10 .20 (Result need: 010110.20) same as above 0101 10 .30 (Result need: 010110.20) same as above 2. Col. C 101 (Result need: 0101) 010110 (Result OK) 01011010 (Result OK) 01011020 (Result OK) 01011030 (Result OK) When I paste the formula in cells the figures changes as shown above. Figures should not be change in any manner. Once again Thank you very much. "JMay" wrote: 1) =LEFT(A1,LEN(A1)-2)&"."&RIGHT(A1,2) 2) =SUBSTITUTE(A1," ","") Copy down in each case "ng6971" wrote: Hi All, I have a excel sheet contains different values in following format: Col. A 0101 0101 10 0101 10 10 0101 10 20 0101 10 30 and so on. Results need in two separate formats in following manner: 1. Col. B 01.01 0101.10 010110.10 010110.20 010110.30 2. Col. C 0101 010110 01011010 01011020 01011030 How can I do this with a code? Thanks in advance. |
Change in Cell Value
Hello JMay,
Let me explain you in more details. I have a list which has 1. 0101 (4 digit value) 2. 0101 00 (6 digit value) 3. 0101 00 10 (8 digit value) What I want to do for 1st criteria: 1. Where as 4 digit value in column put decimal after 2 digits. 2. Where as 6 digit value [there is a space after 4 digits] remove the space and put decimal after 4 digits. 3. Where as 8 digit value [remove space after 4 and 6 digits and put decimal after 6 digits. In 2nd crieteria: 1. Where as 4 digit value in column the value should be 0000. 2. Where as 6 digit value in column the value should be 000000. 3. Where as 8 digit value in column the value should be 00000000. The results of the values should not changed in any manner. Thanks. "ng6971" wrote: Hello JMay, Thanks for quick response. After your suggestion the results a 1. Col. B 1.01 (Result need: 01.01) 0101 .10 (Result need: 0101.10) remove all spaces (figures not changed) 0101 10 .10 (Result need: 010110.10) same as above 0101 10 .20 (Result need: 010110.20) same as above 0101 10 .30 (Result need: 010110.20) same as above 2. Col. C 101 (Result need: 0101) 010110 (Result OK) 01011010 (Result OK) 01011020 (Result OK) 01011030 (Result OK) When I paste the formula in cells the figures changes as shown above. Figures should not be change in any manner. Once again Thank you very much. "JMay" wrote: 1) =LEFT(A1,LEN(A1)-2)&"."&RIGHT(A1,2) 2) =SUBSTITUTE(A1," ","") Copy down in each case "ng6971" wrote: Hi All, I have a excel sheet contains different values in following format: Col. A 0101 0101 10 0101 10 10 0101 10 20 0101 10 30 and so on. Results need in two separate formats in following manner: 1. Col. B 01.01 0101.10 010110.10 010110.20 010110.30 2. Col. C 0101 010110 01011010 01011020 01011030 How can I do this with a code? Thanks in advance. |
| All times are GMT +1. The time now is 12:56 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com