Convert in 2 digits or up to 1 decimal place

[hsandeep]

Column A1:A999 contains dates. Output required in B1:B999 in 2 digits or decimal as:

For
1 01
12 12
9 09
1.5 1.5
10.5 10.5
22.5 22.5
22 22

A1:A999: Will contain only +ve integers or decimal upto 1 place
B1:B999: SHOULD contain 2 digits or decimal upto 1 place

 

[dermie_72]

The best I could come up with was to make B1 = A1, drag down.

Then highligh B and press control + 1

Format cell custom

and choose 00.0 (zero zero point zero)

OK

This gived everything 1 decimal point though.

If in Cell C1 you use:

=if(find(".",A1,1),"Decimal","")

Fill down,

then filter column C, for "#Value!" you can highlight that to re format cell to 00

Little bit long winded but it works.

 

[hsandeep]

Help me with an Excel formula, please.

 

[manojrf]

Hi,

I hope this would help you.

Sheet1

*	A	B

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]01[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]12[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]05[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"]1.5[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]10.5[/TD]
[TD="align: right"]10.5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]22.5[/TD]
[TD="align: right"]22.5[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]22[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B1 =IF(ISERROR(FIND(".",A1)),TEXT(A1,"00"),A1) B2 =IF(ISERROR(FIND(".",A2)),TEXT(A2,"00"),A2) B3 =IF(ISERROR(FIND(".",A3)),TEXT(A3,"00"),A3) B4 =IF(ISERROR(FIND(".",A4)),TEXT(A4,"00"),A4) B5 =IF(ISERROR(FIND(".",A5)),TEXT(A5,"00"),A5) B6 =IF(ISERROR(FIND(".",A6)),TEXT(A6,"00"),A6) B7 =IF(ISERROR(FIND(".",A7)),TEXT(A7,"00"),A7) <tbody> </tbody>

<tbody>
</tbody>

Column A is where you enter the data and the result is shown in Column B

Excel tables to the web >> Excel Jeanie HTML 4

Regards,

Manuel