format inplied decimal point

[Guest]

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]

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

 

[Ron Rosenfeld]

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

 

[Guest]

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]

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

 

[Guest]

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]

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.

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

 

[Guest]

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]

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)

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