Value Changes

[ng6971]

Hi Everyone,

Here is the values in column A. When I put a formula in Column B:

=LEFT(A2,LEN(A2)-2)&"."&RIGHT(A2,2)

Results:

<TABLE style="WIDTH: 193pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=258 x:str><COLGROUP><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4064" width=127><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4192" width=131><TBODY><TR style="HEIGHT: 10.5pt" height=14><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 95pt; HEIGHT: 10.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl26 height=14 width=127>Col. A</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 98pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27 width=131>Col. B</TD></TR><TR style="HEIGHT: 10.5pt" height=14><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 95pt; HEIGHT: 10.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 height=14 width=127 x:num="101">0101</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27 x:fmla='=LEFT(A2,LEN(A2)-2)&"."&RIGHT(A2,2)'>1.01</TD></TR><TR style="HEIGHT: 10.5pt" height=14><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 95pt; HEIGHT: 10.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25 height=14 width=127>0101 10</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27 x:fmla='=LEFT(A3,LEN(A3)-2)&"."&RIGHT(A3,2)'>0101 .10</TD></TR><TR style="HEIGHT: 10.5pt" height=14><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 95pt; HEIGHT: 10.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25 height=14 width=127>0101 10 10</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27 x:fmla='=LEFT(A4,LEN(A4)-2)&"."&RIGHT(A4,2)'>0101 10 .10</TD></TR><TR style="HEIGHT: 10.5pt" height=14><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 95pt; HEIGHT: 10.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25 height=14 width=127>0101 90</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27 x:fmla='=LEFT(A5,LEN(A5)-2)&"."&RIGHT(A5,2)'>0101 .90</TD></TR><TR style="HEIGHT: 10.5pt" height=14><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 95pt; HEIGHT: 10.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25 height=14 width=127>0101 90 10</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27 x:fmla='=LEFT(A6,LEN(A6)-2)&"."&RIGHT(A6,2)'>0101 90 .10</TD></TR><TR style="HEIGHT: 10.5pt" height=14><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 95pt; HEIGHT: 10.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 height=14 width=127 x:num="102">0102</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27 x:fmla='=LEFT(A7,LEN(A7)-2)&"."&RIGHT(A7,2)'>1.02</TD></TR><TR style="HEIGHT: 10.5pt" height=14><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 95pt; HEIGHT: 10.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25 height=14 width=127>0102 10</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27 x:fmla='=LEFT(A8,LEN(A8)-2)&"."&RIGHT(A8,2)'>0102 .10</TD></TR><TR style="HEIGHT: 10.5pt" height=14><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 95pt; HEIGHT: 10.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25 height=14 width=127>0102 10 10</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27 x:fmla='=LEFT(A9,LEN(A9)-2)&"."&RIGHT(A9,2)'>0102 10 .10</TD></TR><TR style="HEIGHT: 10.5pt" height=14><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 95pt; HEIGHT: 10.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25 height=14 width=127>0102 90</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27 x:fmla='=LEFT(A10,LEN(A10)-2)&"."&RIGHT(A10,2)'>0102 .90</TD></TR><TR style="HEIGHT: 10.5pt" height=14><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 95pt; HEIGHT: 10.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25 height=14 width=127>0102 90 10</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27 x:fmla='=LEFT(A11,LEN(A11)-2)&"."&RIGHT(A11,2)'>0102 90 .10</TD></TR><TR style="HEIGHT: 10.5pt" height=14><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 95pt; HEIGHT: 10.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25 height=14 width=127>0102 90 20</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27 x:fmla='=LEFT(A12,LEN(A12)-2)&"."&RIGHT(A12,2)'>0102 90 .20</TD></TR></TBODY></TABLE>

Results need :

<TABLE style="WIDTH: 83pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=110 x:str><COLGROUP><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 3520" width=110><TBODY><TR style="HEIGHT: 10.5pt" height=14><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 83pt; HEIGHT: 10.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 height=14 width=110>Col. B</TD></TR><TR style="HEIGHT: 10.5pt" height=14><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 10.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25 height=14 x:num="1.01">01.01</TD></TR><TR style="HEIGHT: 10.5pt" height=14><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 10.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl26 height=14 x:num="101.1">0101.10</TD></TR><TR style="HEIGHT: 10.5pt" height=14><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 10.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27 height=14 x:num="10110.1">010110.10</TD></TR><TR style="HEIGHT: 10.5pt" height=14><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 10.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl26 height=14 x:num="101.9">0101.90</TD></TR><TR style="HEIGHT: 10.5pt" height=14><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 10.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27 height=14 x:num="10190.1">010190.10</TD></TR><TR style="HEIGHT: 10.5pt" height=14><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 10.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25 height=14 x:num="1.02">01.02</TD></TR><TR style="HEIGHT: 10.5pt" height=14><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 10.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl26 height=14 x:num="102.1">0102.10</TD></TR><TR style="HEIGHT: 10.5pt" height=14><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 10.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27 height=14 x:num="10210.1">010210.10</TD></TR><TR style="HEIGHT: 10.5pt" height=14><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 10.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl26 height=14 x:num="102.9">0102.90</TD></TR><TR style="HEIGHT: 10.5pt" height=14><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 10.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27 height=14 x:num="10290.1">010290.10</TD></TR><TR style="HEIGHT: 10.5pt" height=14><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 10.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27 height=14 x:num="10290.2">010290.20</TD></TR></TBODY></TABLE>

Thanks in advance.

 

[Warship]

=text(substitute(left(a2,len(a2)-2)&"."&right(a2,2)," ",""),"#.##")

 

[shift-del]

Or this:

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

 

[ng6971]

Hi Shift-Del,

Code works. But where the value is in col. A that is

0101
0102
0201
0202
0301
0304 .....

upto 0999

the results are

1.01
1.02
2.01
2.02
3.01
3.04.......

upto 9.99

Note: In formula bar also for col. A the value shows as 101 for 0101.

So still want to correct code for abovesaid values and final results are below :

01.01
01.02
02.01
02.02
03.01
03.04......

upto 09.99

Thanks

 

[shift-del]

Hi

I assumed column A is text-formatted.
Instead you used something like 0000.

I recommend that you change the format to Text.

Otherwise you have to replace every instance of A2 in my formula with this:

IF(ISNUMBER(A2),TEXT(A2,"0000"),A2)

 

[ng6971]

Sir thank you very much.