convert mainframe data to a number in Excel 1

[StanKorn]

I need to convert the following data into a number. It is from a mainframe using a standard TSO app. The data represents a $ amount with 2 decimals but comes thrugh as the following:

0000007288D
0000007288M
0000007288D
0000024553{

If anyone can advise I would be grateful
TIA,
Stan

Remember - only dead fish go with the flow.

 

[franklin97355]

What is the dollar amount this represents?

 

[StanKorn]

I believe it is 0000007288D = 72.88 but can't telln if it is Debit or credit.
tia,S


Remember - only dead fish go with the flow.

 

[fredericofonseca]

I will be able to give a function to convert that to numbers.

But it basically the last digit is either negative or positive, and the varies according to the underlying number.

e.g. 1 is P, 2 is O and so on (just an example).

Just need to search for it as I don't have it hand...

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[StanKorn]

The text file is this:

073105AI0705411119000041401018070000 CY078A0000007288D 00355180700
073105AI0705411130020011401018070000 CY078A0000007288M 00355180700
073105AI0705411130020021401018070000 CY078A0000007288D 00355180700
073105AI0705411119000041401022550000 CY078A0000024553{ 00355225500

The XLS file of the text translation is this:

073105 AI 0705 411 11900004 14 01 0 1807 0000 CY078A 0000007288D 003 55 180700
073105 AI 0705 411 13002001 14 01 0 1807 0000 CY078A 0000007288M 003 55 180700
073105 AI 0705 411 13002002 14 01 0 1807 0000 CY078A 0000007288D 003 55 180700
073105 AI 0705 411 11900004 14 01 0 2255 0000 CY078A 0000024553{ 003 55 225500

The field in question ends with a text character such as 0000007288D or 0000024553{.
I hope this explains it a litle better.
Stan


Remember - only dead fish go with the flow.

 

[fredericofonseca]

no need to explain. It is perfectly clear.

as I said I will supply you the full explanation and function to convert this.

Another way is to ask your mainframe people to supply you with a file with separated (leading or trailing) signal.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[fredericofonseca]

Positive numbers

0={
1=A
2=B
3=C
4=D
5=E
6=F
7=G
8=H
9=I

Negative numbers

0=}
1=J
2=K
3=L
4=M
5=N
6=O
7=P
8=Q
9=R

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[StanKorn]

Thanks Frederico.
Stan-+

Remember - only dead fish go with the flow.

 

[SkipVought]

Stan,

It's been a while since I have looked at various mainframe numeric storage techniques.

The last character in the string can be decoded in EBCDIC
[tt]
Dec Hex EBCDIC
193 C1 A A
194 C2 B B
195 C3 C C
196 C4 D D
197 C5 E E
198 C6 F F
199 C7 G G
200 C8 H H
201 C9 I I
209 D1 J J
210 D2 K K
211 D3 L L
212 D4 M M
213 D5 N N
214 D6 O O
215 D7 P P
216 D8 Q Q
217 D9 R R
[/tt]
The last nibble is numeric. The first, C or D indicates + or - value.

So "decode" the rightmost byte in a table
[tt]
Cha Val Sgn
A 1 1
B 2 1
C 3 1
D 4 1
E 5 1
F 6 1
G 7 1
H 8 1
I 9 1
J 1 -1
K 2 -1
L 3 -1
M 4 -1
N 5 -1
O 6 -1
P 7 -1
Q 8 -1
R 9 -1
{ 0 1
[/tt]
or it may be the other way around with the sign -- I can't remember. Ask one of your mf guys.

I name the tange in my table using Insert/Name/Create - Create names in TOP row.

So if the mf value is in E1...
[tt]
=(VALUE(LEFT(E1,LEN(E1)-1))*10+INDEX(Val,MATCH(RIGHT(E1,1),Cha,0),1))*INDEX(Sgn,MATCH(RIGHT(E1,1),Cha,0),1)/100[/tt]




Skip,
[sub]
[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTHFORTHWITH![/sub]

 

[fredericofonseca]

Skip, you are forgetting the negative ZERO on your list.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[SkipVought]

I noticed!

Skip,
[sub]
[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTHFORTHWITH![/sub]

 

[fredericofonseca]

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[SkipVought]

BTW,

I like to know WHY and HOW things happen, so seeing the EBCDIC Hex turns on the light for me.

Another interesting thing about EBCDIC, converting lower case to upper case: you OR a SPACE (Hex 40)
[tt]
Hex EBCDIC Hex EBCDIC
81 a C1 A
82 b C2 B
83 c C3 C
84 d C4 D
85 e C5 E
86 f C6 F
87 g C7 G
88 h C8 H
89 i C9 I
91 j D1 J
92 k D2 K
93 l D3 L
94 m D4 M
95 n D5 N
96 o D6 O
97 p D7 P
98 q D8 Q
99 r D9 R
A2 s E2 S
A3 t E3 T
A4 u E4 U
A5 v E5 V
A6 w E6 W
A7 x E7 X
A8 y E8 Y
A9 z E9 Z
[/tt]


Skip,
[sub]
[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTHFORTHWITH![/sub]

 

[fredericofonseca]

here is the function.

Add it to a module, and then reference it on a cell as a formula "=conv_sign(A1)" (for example)

Function conv_sign(celval As Range) As Variant
Dim j As Integer
Dim s As String
Dim x As String
Dim sign As Long
i = 0
j = 0
sign = 1

s = Trim(celval.Value)

If Len(s) = 0 Then
    conv_sign = 0
    Exit Function
End If

If IsNumeric(s) Then
    conv_sign = Val(s)
    Exit Function
End If

x = Right(s, 1)

If Len(s) > 1 Then
    If Not IsNumeric(Mid(s, 1, Len(s) - 1)) Then
        conv_sign = 0
        Exit Function
    End If

    j = Val(Mid(s, 1, Len(s) - 1)) * 10
End If
Select Case x
    Case "{": i = 0
    Case "A": i = 1
    Case "B": i = 2
    Case "C": i = 3
    Case "D": i = 4
    Case "E": i = 5
    Case "F": i = 6
    Case "G": i = 7
    Case "H": i = 8
    Case "I": i = 9

    Case "}": i = 0
            sign = -1
    Case "J": i = 1
            sign = -1
    Case "K": i = 2
            sign = -1
    Case "L": i = 3
            sign = -1
    Case "M": i = 4
            sign = -1
    Case "N": i = 5
            sign = -1
    Case "O": i = 6
            sign = -1
    Case "P": i = 7
            sign = -1
    Case "Q": i = 8
            sign = -1
    Case "R": i = 9
            sign = -1
    Case Else
        conv_sign = 0
        Exit Function
End Select

conv_sign = (j + i) * sign
Exit Function

End Function

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com