Does anyone know how to import and convert a signed over-punch text into
Excel? I received a text file and its format includes signed over-punch
chaharacters, e.g. 0025{ = 2.50, also how do I tell Excel about implied
decimal. It can be either part of a vba language or function link for
convertion, preferably both.

Thank you in advance.

Alex,

AFAIK Excel does not have any capability to import overpunched numbers.
Therefore you need to convert them.

One option is to use the following function. The parameters szCode is the
overpunched number and iRadix is the number of decimals.


Option Explicit

Function overpunch(szCode As String, iRadix As Integer) As Double

Dim ans As Double
Dim r As String

Application.Volatile
szCode = UCase(Trim(szCode))
ans = 10 * CDbl(Left(szCode, Len(szCode) - 1))
r = Right(szCode, 1)
Select Case r
Case "A" To "I" ' +ve sign 1..9
ans = ans + Asc(r) - Asc("A") + 1
Case "{" ' +ve sign 0
' no action
Case "J" To "R" ' -ve sign 1..9
ans = -(ans + Asc(r) - Asc("J") + 1)
Case "}" ' -ve sign 0
ans = -ans
Case "0" To "9" ' unsigned
' no action
End Select
overpunch = ans / (10 ^ iRadix)
End Function
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Alex" wrote:

Does anyone know how to import and convert a signed over-punch text into
Excel? I received a text file and its format includes signed over-punch
chaharacters, e.g. 0025{ = 2.50, also how do I tell Excel about implied
decimal. It can be either part of a vba language or function link for
convertion, preferably both.

Thank you in advance.

Can it be nested inside a procedure or called? Where in a stsatement will it
go? Below is an example of a Macro used to import the overpunch:

Sub VBA()
'
' VBA Macro
' Macro recorded 1/3/2007 by Alex
'

'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\Alex.IPC\Desktop\DLTest.txt",
Destination:= _
Range("A1"))
.Name = "DLTest"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 5, 2, 2, 2, 2, 2, 2,
1, 1, 1, 1, 1, 2, 2, 5, 2, _
2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 5, 2, 2, 2, 2, 2, 2, 2, 2, 2,
2, 2, 2, 2, 1, 2, 1, 2, 2, 2, 1, 5)
.TextFileFixedColumnWidths = Array(1, 10, 5, 12, 7, 8, 11, 30, 2, 5,
3, 2, 6, 6, 6, 6, 6, 12, 15, 8, 1, _
18, 1, 15, 3, 3, 10, 6, 12, 15, 12, 7, 2, 2, 8, 1, 3, 1, 1, 1, 2,
2, 1, 2, 10, 5, 1, 15, 4, 1, 6, 1, 9, 26, 6, 6)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
ActiveWindow.SmallScroll Down:=-9
Range("A1").Select
Range("A1:AF69").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Call VBAHeader


End Sub


"Martin Fishlock" wrote:

Alex,

AFAIK Excel does not have any capability to import overpunched numbers.
Therefore you need to convert them.

One option is to use the following function. The parameters szCode is the
overpunched number and iRadix is the number of decimals.


Option Explicit

Function overpunch(szCode As String, iRadix As Integer) As Double

Dim ans As Double
Dim r As String

Application.Volatile
szCode = UCase(Trim(szCode))
ans = 10 * CDbl(Left(szCode, Len(szCode) - 1))
r = Right(szCode, 1)
Select Case r
Case "A" To "I" ' +ve sign 1..9
ans = ans + Asc(r) - Asc("A") + 1
Case "{" ' +ve sign 0
' no action
Case "J" To "R" ' -ve sign 1..9
ans = -(ans + Asc(r) - Asc("J") + 1)
Case "}" ' -ve sign 0
ans = -ans
Case "0" To "9" ' unsigned
' no action
End Select
overpunch = ans / (10 ^ iRadix)
End Function
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Alex" wrote:

Does anyone know how to import and convert a signed over-punch text into
Excel? I received a text file and its format includes signed over-punch
chaharacters, e.g. 0025{ = 2.50, also how do I tell Excel about implied
decimal. It can be either part of a vba language or function link for
convertion, preferably both.

Thank you in advance.