Inserting Hex values into Excel

35 pts.
Tags:
Concatenate
excel
Excel formulas
Excel functions
Hexadecimal values
Im new to Excel and am trying to Concatenate a number of cells together and sperate them by a single character represented by a Hex "4F". The combined cells will then be written to a file on my PCs C drive by way of a script. I will Transfer the file to a Mainframe PDS. Ive used HEX2DEC and its variations, but it always takes up two positions in the PC file and is also incorrect in the Mainframe file. How can I get the single hex character to appear? Any help would be appreciated.
ASKED: October 4, 2010  9:51 PM
UPDATED: November 10, 2010  5:27 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • BCP
    Have you tried using a verticle bar (|) ASCII 7c in your concatenation. That should translate to a verticle bar (|) Ebcdic 4f on upload. Of course this assumes that your mainframe is an EBCDIC machine.
    65 pointsBadges:
    report
  • Marsh2183
    BCP; I've tried the verticle bar character, a cell reference with CHAR(79), CODE("I"), CHAR(124) which is the numerical value of the verticle bar & dec2hex(124). I either get a value that takes up 2 positions (ie dec2hex(124) = 7c, or a sigle value like CAHR(79 = O, which after the FTP look like 7c or O. If CODE("|") is 124 & CHAR(124) is veritcle bar, then when used as the delimiting character it looks fine when viewed in EXCEL, but after the FTP it is a hex 6A (a broken verticle line) and I need to manually change it to an X"4F". Maybe I'm typing the formula incorrectly. Can you show me what you think it should be. Will try anthing at this point.
    35 pointsBadges:
    report
  • BCP
    It looks like the ASCII to EBCDIC translation table you are using is not the standard one supplied by IBM which translates ; ASCII-to-EBCDIC table ; ; 00 01 02 03 04 05 06 07 08 09 0A 0B 0C 0D 0E 0F ; 00 01 02 03 37 2D 2E 2F 16 05 25 0B 0C 0D 0E 0F ; 00 ; 10 11 12 13 3C 3D 32 26 18 19 3F 27 22 1D 35 1F ; 10 ; 40 5A 7F 7B 5B 6C 50 7D 4D 5D 5C 4E 6B 60 4B 61 ; 20 ; F0 F1 F2 F3 F4 F5 F6 F7 F8 F9 7A 5E 4C 7E 6E 6F ; 30 ; 7C C1 C2 C3 C4 C5 C6 C7 C8 C9 D1 D2 D3 D4 D5 D6 ; 40 ; D7 D8 D9 E2 E3 E4 E5 E6 E7 E8 E9 AD E0 BD 5F 6D ; 50 ; 79 81 82 83 84 85 86 87 88 89 91 92 93 94 95 96 ; 60 ; 97 98 99 A2 A3 A4 A5 A6 A7 A8 A9 C0 4F D0 A1 07 ; 70 ; 00 01 02 03 37 2D 2E 2F 16 05 25 0B 0C 0D 0E 0F ; 80 ; 10 11 12 13 3C 3D 32 26 18 19 3F 27 22 1D 35 1F ; 90 ; 40 5A 7F 7B 5B 6C 50 7D 4D 5D 5C 4E 6B 60 4B 61 ; A0 ; 3 F0 F1 F2 F3 F4 F5 F6 F7 F8 F9 7A 5E 4C 7E 6E 6F ; B0 ; 7C C1 C2 C3 C4 C5 C6 C7 C8 C9 D1 D2 D3 D4 D5 D6 ; C0 ; D7 D8 D9 E2 E3 E4 E5 E6 E7 E8 E9 AD E0 BD 5F 6D ; D0 ; 79 81 82 83 84 85 86 87 88 89 91 92 93 94 95 96 ; E0 ; 97 98 99 A2 A3 A4 A5 A6 A7 A8 A9 C0 4F D0 A1 07 ; F0 ; The Excel code I would use is =CONCATENATE(A1,"|",B1,"|",C1) or if you prefer =CONCATENATE(A2,CHAR(124),B2,CHAR(124),C2) The other possibility is that when you save the file in your macro you are saving it in DOS format rather than ANSI. That would convert the solid vertical bar to a broken vertical bar. Hope this helps.
    65 pointsBadges:
    report
  • Marsh2183
    BCP; I tried using CHAR(124) but had the same issue. I've come to the same conclusion about the conversion table but am not sure what my other options are. Selecting the "EBCDIC" option from the Rumba transfer screen doesn't work, but when I don't select it I get the correct pipe character but everything else garbage. Do you know of an option I could use when performing the Rumba transfer that would use another conversion table? I couldn't find one in any of my research.
    35 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following