Change in Cell Value

  • Thread starter Thread starter ng6971
  • Start date Start date
N

ng6971

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.
 
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),".",RIGHT(A24,2))

will output 0101.10.20
 
Hello JMay,

Thanks for quick response. After your suggestion the results are:

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.
 
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.
 
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.
 
Back
Top