This is what I use. It can be refined for sure but it does work.
PROCEDURE spnCol2cCol
LPARAMETERS vnCol
LOCAL lnInt1, lnInt2, lnInt3, lcColumn
STORE "" TO lcColumn
lcColumn = ""
DO CASE
CASE m.vnCol > 1378
STORE m.vnCol - 703 TO vnCol
m.lnInt1 = INT(m.vnCol / 676) + 1 && Plus 1 for the 702 component
STORE m.vnCol - (m.lnInt1 - 1) * 676 TO m.vnCol
STORE INT(m.vnCol / 26) + 1 TO lnInt2
STORE mod(m.vnCol, 26) + 1 TO lnInt3
lcColumn = CHR(64 + m.lnInt1) + CHR(64 + m.lnInt2) + CHR(64 +
m.lnInt3)
CASE m.vnCol > 702
lnInt1 = 1 && "A"
lnInt2 = CEILING((m.vnCol - 702) / 26)
lnInt3 = m.vnCol - 702 - (m.lnInt2 - 1) * 26
lcColumn = CHR(64 + m.lnInt1) + CHR(64 + m.lnInt2) + CHR(64 +
m.lnInt3)
CASE m.vnCol > 26
lnInt1 = INT((m.vnCol - 1) / 26)
lnInt2 = m.vnCol - m.lnInt1 * 26
lcColumn = CHR(64 + m.lnInt1) + CHR(64 + m.lnInt2)
OTHERWISE
lcColumn = CHR(64 + m.vnCol)
ENDCASE
RETURN lcColumn
ENDPROC
-----Original Message-----
From: ProfoxTech [mailto:[email protected]] On Behalf Of
[email protected]
Sent: Wednesday, 17 August 2016 6:27 AM
To: [email protected]
Subject: Better way of determining resulting Excel column from integer?
(Retrying to send this for the 3rd time; this time I trimmed the bottom)
I've got a need to know which Excel column a value will be placed using an
integer number. I came up with this routine below but it seems klunky, and
although it works, I was wondering if someone had a better way?
FUNCTION GetColumn(tiNumber as Integer) as String LOCAL liMultiples as
Integer, lcColumn as String, liLeftover as Integer liMultiples =
INT((tiNumber-1)/26) liLeftover = MOD(tiNumber,26) IF liLeftover <> 0 THEN
lcColumn = CHR(liLeftover+64)
ELSE
lcColumn = "Z"
ENDIF
IF liMultiples >= 1 THEN
lcColumn = CHR(liMultiples + 64) + lcColumn ENDIF && liMultiples >=
1 RETURN lcColumn ENDFUNC && GetColumnLetter(tiNumber as Integer) as String
I'm trying to produce meta-code that will create the Excel outputs from a
fixed width input file, and hence my need to know which column I will be
entering the information per record.
Here's the resulting output of code that uses this routine to generate the
fields to process. The routine above determined the value for the "Column"
value:
Num Column Range Length Description
1 A 01-06 6 PROVIDER NUMBER
2 B 07-17 11 MEDICAL RECORD
NUMBER
(STANDARDIZED)
3 C 18-19 2 ADMIT MONTH (MM)
4 D 20-21 2 ADMIT DATE (DD)
5 E 22-25 4 ADMIT YEAR (CCYY)
6 F 26-27 2 DISCHARGE MONTH (MM)
7 G 28-29 2 DISCHARGE DATE (DD)
8 H 30-33 4 DISCHARGE YEAR
(CCYY)
9 I 34-34 1 RECORD TYPE 1
10 J 35-36 2 ADMIT HOUR
11 K 37-37 1 NATURE OF ADMISSION
1=DELIVERY
12 L 38-39 2 SOURCE OF ADMISSION
13 M 40-40 1 ADMIT FROM EMERGENCY
ROOM
1=ADMITTED FROM
14 N 41-42 2 BIRTHDATE MONTH (MM)
15 O 43-44 2 BIRTHDATE DAY (DD)
16 P 45-48 4 BIRTHDATE YEAR
(CCYY)
17 Q 49-49 1 SEX
1=MALE
18 R 50-50 1 RACE
1=WHITE
19 S 51-51 1 ETHNICITY
1=SPANISH/HISPANIC
20 T 52-52 1 MARTIAL STATUS
1=SINGLE
21 U 53-54 2 AREA OF RESIDENCE
COUNTY CODE
22 V 55-59 5 RESIDENCE ZIP CODE
XXXXX ZIP CODE
23 W 60-61 2 PRINCIPAL PAYER
SOURCE
24 X 62-63 2 SECONDARY PAYER
SOURCE
25 Y 64-69 6 CENSUS TRACT
26 Z 70-71 2 DISPOSITION OF
PATIENT
27 AA 72-74 3 ALTERNATIVE RATE
METHOD
ARM CODE
28 AB 75-76 2 SOURCE OF PAYMENT
EXPECTED PAYOR FOR MOST
etc.
[excessive quoting removed by server]
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/[email protected]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
