Cell formatting

[KeMet]

I have two colums of cells where I need the decimal point in the value
eliminated. I am importing weekly hours worked and weekly overtime hours
worked from a text file. They are imported in this fashion, 80.00 and 1.18
for overtime. I have to use the custom function to format the data like this:
0080.0000 and 0001.180. I do not need the decimal points (and I don't want to
round the values), but I still need the data as 0080000 and 0001180.
Any help on this is most appreciated.

Thanks,
Kyle

[Jim May]

create new helper column and enter:
=TEXT(A1*1000,"0000000")
You could also Copy and Paste-Special Value
to rid yourself of the formula afterwards..
Jim

"KeMet" wrote:

> I have two colums of cells where I need the decimal point in the value
> eliminated. I am importing weekly hours worked and weekly overtime hours
> worked from a text file. They are imported in this fashion, 80.00 and 1.18
> for overtime. I have to use the custom function to format the data like this:
> 0080.0000 and 0001.180. I do not need the decimal points (and I don't want to
> round the values), but I still need the data as 0080000 and 0001180.
> Any help on this is most appreciated.
>
> Thanks,
> Kyle

[KeMet]

Thanks, but I can't create another column. I am using this with some payroll
interface, so I need to save the .xls file as a .txt file. If I hide the
columns in excel, they still appear in the text file.

I'll keep trying, thanks.

Kyle


"Jim May" wrote:

> create new helper column and enter:
> =TEXT(A1*1000,"0000000")
> You could also Copy and Paste-Special Value
> to rid yourself of the formula afterwards..
> Jim
>
> "KeMet" wrote:
>
> > I have two colums of cells where I need the decimal point in the value
> > eliminated. I am importing weekly hours worked and weekly overtime hours
> > worked from a text file. They are imported in this fashion, 80.00 and 1.18
> > for overtime. I have to use the custom function to format the data like this:
> > 0080.0000 and 0001.180. I do not need the decimal points (and I don't want to
> > round the values), but I still need the data as 0080000 and 0001180.
> > Any help on this is most appreciated.
> >
> > Thanks,
> > Kyle

[JE McGimpsey]

If you follow the second part of Jim's suggestion, you don't need to
keep the additional column. Use the helper column to create the
formatted text, copy the column, then select your original column and
choose Edit/Paste Special, selecting the Values radio button. Delete the
"helper" column, and you're good to go.

In article <[email protected]>,
KeMet <[email protected]> wrote:

> Thanks, but I can't create another column. I am using this with some payroll
> interface, so I need to save the .xls file as a .txt file. If I hide the
> columns in excel, they still appear in the text file.
>
> I'll keep trying, thanks.
>
> Kyle
>
>
> "Jim May" wrote:
>
> > create new helper column and enter:
> > =TEXT(A1*1000,"0000000")
> > You could also Copy and Paste-Special Value
> > to rid yourself of the formula afterwards..
> > Jim

[KeMet]

That did it. Thank you so much to the both of you.

Kyle


"JE McGimpsey" wrote:

> If you follow the second part of Jim's suggestion, you don't need to
> keep the additional column. Use the helper column to create the
> formatted text, copy the column, then select your original column and
> choose Edit/Paste Special, selecting the Values radio button. Delete the
> "helper" column, and you're good to go.
>
> In article <[email protected]>,
> KeMet <[email protected]> wrote:
>
> > Thanks, but I can't create another column. I am using this with some payroll
> > interface, so I need to save the .xls file as a .txt file. If I hide the
> > columns in excel, they still appear in the text file.
> >
> > I'll keep trying, thanks.
> >
> > Kyle
> >
> >
> > "Jim May" wrote:
> >
> > > create new helper column and enter:
> > > =TEXT(A1*1000,"0000000")
> > > You could also Copy and Paste-Special Value
> > > to rid yourself of the formula afterwards..
> > > Jim
>