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

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.

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.

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

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

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