format inplied decimal point

I am importing a fixed length text file containing numeric data with an
implied decimal point. I want to format the worksheet column to include the
decimal point. E.g. text file '000012345', worksheet column '0000123.45'

I can format the column as numeric to include the decimal point and divide
the value by 100 to get the precision. I am not sure how to define the
calculation for the column.

I'm assuming that dividing by 100 is the easiest way to do this. Let me know
if there is an easier way.

Thanks,
Tom

If you're doing this by hand, you can find an empty cell.
Put 100 in that cell
edit|copy that cell
select the range to fix
edit|paste special|check divide
Then clean up that helper cell (with 100 in it).

In code, it could look something like:

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCell As Range

With ActiveSheet
Set myCell = .Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 1)
myCell.Value = 100

Set myRng = .Range("e1", .Cells(.Rows.Count, "E").End(xlUp))

myCell.Copy
myRng.PasteSpecial operation:=xlPasteSpecialOperationDivide

myCell.Clear
End With

End Sub

(I used column E for my testing.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

c62ip64 wrote:
>
> I am importing a fixed length text file containing numeric data with an
> implied decimal point. I want to format the worksheet column to include the
> decimal point. E.g. text file '000012345', worksheet column '0000123.45'
>
> I can format the column as numeric to include the decimal point and divide
> the value by 100 to get the precision. I am not sure how to define the
> calculation for the column.
>
> I'm assuming that dividing by 100 is the easiest way to do this. Let me know
> if there is an easier way.
>
> Thanks,
> Tom

--

Dave Peterson

On Mon, 14 Nov 2005 11:47:06 -0800, "c62ip64"
<c62ip64@discussions.microsoft.com> wrote:

>I am importing a fixed length text file containing numeric data with an
>implied decimal point. I want to format the worksheet column to include the
>decimal point. E.g. text file '000012345', worksheet column '0000123.45'
>
>I can format the column as numeric to include the decimal point and divide
>the value by 100 to get the precision. I am not sure how to define the
>calculation for the column.
>
>I'm assuming that dividing by 100 is the easiest way to do this. Let me know
>if there is an easier way.
>
>Thanks,
>Tom

Yes, dividing by 100 is the simplest method.

You can set up a "helper column" with the formula =cell_ref/100.
Copy/Drag down as far as needed
Copy/Paste Special Values to convert the formula to numbers (pasting back over
the original).

You can also type 100 in some blank cell.
Edit/Copy the cell with the 100 in it.
Select the range of numbers to be converted.
Edit/Paste Special Divide


--ron

Thanks, I'm getting an 'invalid name error' when using the formula but the
second option formats the value with the decimal point placement.

I have one additional question about formatting negative values. How do I
format a negative value from the test file, '0005400}', as '-000540.00'.

Tom

"Ron Rosenfeld" wrote:

> On Mon, 14 Nov 2005 11:47:06 -0800, "c62ip64"
> <c62ip64@discussions.microsoft.com> wrote:
>
> >I am importing a fixed length text file containing numeric data with an
> >implied decimal point. I want to format the worksheet column to include the
> >decimal point. E.g. text file '000012345', worksheet column '0000123.45'
> >
> >I can format the column as numeric to include the decimal point and divide
> >the value by 100 to get the precision. I am not sure how to define the
> >calculation for the column.
> >
> >I'm assuming that dividing by 100 is the easiest way to do this. Let me know
> >if there is an easier way.
> >
> >Thanks,
> >Tom
>
> Yes, dividing by 100 is the simplest method.
>
> You can set up a "helper column" with the formula =cell_ref/100.
> Copy/Drag down as far as needed
> Copy/Paste Special Values to convert the formula to numbers (pasting back over
> the original).
>
> You can also type 100 in some blank cell.
> Edit/Copy the cell with the 100 in it.
> Select the range of numbers to be converted.
> Edit/Paste Special Divide
>
>
> --ron
>

On Tue, 15 Nov 2005 06:06:01 -0800, "c62ip64"
<c62ip64@discussions.microsoft.com> wrote:

>Thanks, I'm getting an 'invalid name error' when using the formula but the
>second option formats the value with the decimal point placement.

You would get a #NAME error if the cell reference that you substituted for
cell_ref in the formula was not valid in your worksheet.

>
>I have one additional question about formatting negative values. How do I
>format a negative value from the test file, '0005400}', as '-000540.00'.

How do you know that the value is negative?


--ron

The sign is contained in the low order bits of the last character.
x'D0' - } translates to -0
x'D1' - J translates to -1
x'D2' - K translates to -2
x'D3' - L translates to -3
etc.

Sounds like Excel does not recognize this format and I'll need a script to
interpret the results.

Thanks,
Tom

"Ron Rosenfeld" wrote:

> On Tue, 15 Nov 2005 06:06:01 -0800, "c62ip64"
> <c62ip64@discussions.microsoft.com> wrote:
>
> >Thanks, I'm getting an 'invalid name error' when using the formula but the
> >second option formats the value with the decimal point placement.
>
> You would get a #NAME error if the cell reference that you substituted for
> cell_ref in the formula was not valid in your worksheet.
>
> >
> >I have one additional question about formatting negative values. How do I
> >format a negative value from the test file, '0005400}', as '-000540.00'.
>
> How do you know that the value is negative?
>
>
> --ron
>

You could probably process it using the SUBSTITUTE worksheet function. If you
post some examples of inputs and associated outputs, I can help work it out.



On Tue, 15 Nov 2005 12:38:01 -0800, "c62ip64"
<c62ip64@discussions.microsoft.com> wrote:

>The sign is contained in the low order bits of the last character.
>x'D0' - } translates to -0
>x'D1' - J translates to -1
>x'D2' - K translates to -2
>x'D3' - L translates to -3
>etc.
>
>Sounds like Excel does not recognize this format and I'll need a script to
>interpret the results.
>
>Thanks,
>Tom
>
>"Ron Rosenfeld" wrote:
>
>> On Tue, 15 Nov 2005 06:06:01 -0800, "c62ip64"
>> <c62ip64@discussions.microsoft.com> wrote:
>>
>> >Thanks, I'm getting an 'invalid name error' when using the formula but the
>> >second option formats the value with the decimal point placement.
>>
>> You would get a #NAME error if the cell reference that you substituted for
>> cell_ref in the formula was not valid in your worksheet.
>>
>> >
>> >I have one additional question about formatting negative values. How do I
>> >format a negative value from the test file, '0005400}', as '-000540.00'.
>>
>> How do you know that the value is negative?
>>
>>
>> --ron
>>

--ron

ok, here is an example of the input. There are 3 numerics, each numeric is 11
positions with 2 decimal places.

'000000152350000243577Q00000000000'

The row output, 3 cells, is the following:

152.35 -24,357.78 0.00

Thanks,
Tom




"Ron Rosenfeld" wrote:

> You could probably process it using the SUBSTITUTE worksheet function. If you
> post some examples of inputs and associated outputs, I can help work it out.
>
>
>
> On Tue, 15 Nov 2005 12:38:01 -0800, "c62ip64"
> <c62ip64@discussions.microsoft.com> wrote:
>
> >The sign is contained in the low order bits of the last character.
> >x'D0' - } translates to -0
> >x'D1' - J translates to -1
> >x'D2' - K translates to -2
> >x'D3' - L translates to -3
> >etc.
> >
> >Sounds like Excel does not recognize this format and I'll need a script to
> >interpret the results.
> >
> >Thanks,
> >Tom
> >
> >"Ron Rosenfeld" wrote:
> >
> >> On Tue, 15 Nov 2005 06:06:01 -0800, "c62ip64"
> >> <c62ip64@discussions.microsoft.com> wrote:
> >>
> >> >Thanks, I'm getting an 'invalid name error' when using the formula but the
> >> >second option formats the value with the decimal point placement.
> >>
> >> You would get a #NAME error if the cell reference that you substituted for
> >> cell_ref in the formula was not valid in your worksheet.
> >>
> >> >
> >> >I have one additional question about formatting negative values. How do I
> >> >format a negative value from the test file, '0005400}', as '-000540.00'.
> >>
> >> How do you know that the value is negative?
> >>
> >>
> >> --ron
> >>
>
> --ron
>

With your 11 position numeric in B1, the formula to convert it should be:


=IF(ISNUMBER(-RIGHT(B1,1)),B1/100,
(LEFT(B1,10)&MOD(CODE(RIGHT(B1,1))-73,52))/-100)




On Wed, 16 Nov 2005 05:44:46 -0800, "c62ip64"
<c62ip64@discussions.microsoft.com> wrote:

>ok, here is an example of the input. There are 3 numerics, each numeric is 11
>positions with 2 decimal places.
>
>'000000152350000243577Q00000000000'
>
>The row output, 3 cells, is the following:
>
>152.35 -24,357.78 0.00
>
>Thanks,
>Tom
>
>
>
>
>"Ron Rosenfeld" wrote:
>
>> You could probably process it using the SUBSTITUTE worksheet function. If you
>> post some examples of inputs and associated outputs, I can help work it out.
>>
>>
>>
>> On Tue, 15 Nov 2005 12:38:01 -0800, "c62ip64"
>> <c62ip64@discussions.microsoft.com> wrote:
>>
>> >The sign is contained in the low order bits of the last character.
>> >x'D0' - } translates to -0
>> >x'D1' - J translates to -1
>> >x'D2' - K translates to -2
>> >x'D3' - L translates to -3
>> >etc.
>> >
>> >Sounds like Excel does not recognize this format and I'll need a script to
>> >interpret the results.
>> >
>> >Thanks,
>> >Tom
>> >
>> >"Ron Rosenfeld" wrote:
>> >
>> >> On Tue, 15 Nov 2005 06:06:01 -0800, "c62ip64"
>> >> <c62ip64@discussions.microsoft.com> wrote:
>> >>
>> >> >Thanks, I'm getting an 'invalid name error' when using the formula but the
>> >> >second option formats the value with the decimal point placement.
>> >>
>> >> You would get a #NAME error if the cell reference that you substituted for
>> >> cell_ref in the formula was not valid in your worksheet.
>> >>
>> >> >
>> >> >I have one additional question about formatting negative values. How do I
>> >> >format a negative value from the test file, '0005400}', as '-000540.00'.
>> >>
>> >> How do you know that the value is negative?
>> >>
>> >>
>> >> --ron
>> >>
>>
>> --ron
>>

--ron