Or, you could ask Excel to do it for you: http://access-excel.tips/convert-column-letter-to-column-number/
and http://access-excel.tips/address-convert-column-number-letter/ On Tue, Aug 16, 2016 at 4:26 PM, <[email protected]> wrote: > (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/cacw6n4s0g3y8a1vwzqxhhrawekwecwgchccpwqe5cn4crtm...@mail.gmail.com ** 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.
