Change in Cell Value

[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.

 

[xl@lf]

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

 

[JMay]

1) =LEFT(A1,LEN(A1)-2)&"."&RIGHT(A1,2)
2) =SUBSTITUTE(A1," ","")

Copy down in each case

 

[ng6971]

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.

 

[ng6971]

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]

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.