topic-38734

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Number to Char like Excel
Posted: Fri Apr 03, 2020 08:52 AM

Hi,

Is there a function that converts a number to a Character-string like excel columns

1 = A
2 = C
...
26 = Z
27 = AA
28 = AB
..
52 = AZ
53 = BA
..

MaxP

Posts: 99

Joined: Thu Jul 12, 2007 02:02 PM

Re: Number to Char like Excel
Posted: Fri Apr 03, 2020 10:58 AM

Try this

Code (fw): Select all Collapse

FUNCTION cGExcCol( nCol )
        LOCAL   cCol := "", nTmp

        IF nCol > 26
                nTmp := INT( nCol / 26 )
                IF nTmp > 26
                        RETURN 0
                ENDIF
                cCol += CHR( ASC( "@" ) + nTmp )
        ENDIF
        
        nTmp := nCol % 26
        IF nTmp > 0
                cCol += CHR( ASC( "@" ) + ( nCol % 26 ) )       
        ELSE
                cCol += "Z" 
        ENDIF        
RETURN cCol

/*****************************************************************************/

FUNCTION nGExcCol( cCol )
        LOCAL   nPos := 0

        cCol := ALLTRIM( cCol )
        IF .NOT. EMPTY( cCol )
                IF LEN( cCol ) > 1
                        nPos := 26 * (ASC( LEFT( cCol, 1 ) ) - ASC( "@" ))
                        cCol := SUBSTR( cCol, 2 )
                ENDIF
                nPos += (ASC( LEFT( cCol, 1 ) ) - ASC( "@" ))
                IF nPos > 230
                        nPos := 0
                ENDIF
        ENDIF
RETURN nPos

Regards,
Massimo

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: Number to Char like Excel
Posted: Fri Apr 03, 2020 11:41 AM

Thank you Massimo, it's working very nice

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Number to Char like Excel
Posted: Sat Apr 04, 2020 05:40 PM

This is the function used by xbrowse.

Code (fw): Select all Collapse

static Function MakeColAlphabet( nCol )

   local cCol  := ""
   local nDigit

   do while nCol > 0
      nDigit   := nCol % 26
      if nDigit == 0
         nDigit   := 26
         nCol     -= 26
      endif
      cCol     := Chr( nDigit + 64 ) + cCol
      nCol     := Int( nCol / 26 )
   enddo

return cCol

Regards

G. N. Rao.

Hyderabad, India

FiveTech Support Forums

Continue the discussion