If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 CHAR funtcion with double letter columns
 Author Name Remember Me? Password
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## CHAR funtcion with double letter columns

 Thread Tools Display Modes
#1
July 16th 07, 01:12 PM posted to microsoft.public.excel.worksheet.functions
 ellebelle external usenet poster Posts: 34
CHAR funtcion with double letter columns

I am using this function:

CHAR(MATCH(P\$1,\$1:\$1,)+64)

to get teh column letter, however it does not count double letters past
column z. As in AA AB etc.

Is there a way to get this?

Ellen
#2
July 16th 07, 01:38 PM posted to microsoft.public.excel.worksheet.functions
 Mike H external usenet poster Posts: 11,501
CHAR funtcion with double letter columns

Try this UDF

Function CLetter(CNumber As Integer) As String
CLetter = Left(Cells(1, CNumber).Address _
(False, False), 1 - (CNumber > 26))
End Function

call it with =cletter(n)

Mike
"ellebelle" wrote:

> I am using this function:
>
> CHAR(MATCH(P\$1,\$1:\$1,)+64)
>
> to get teh column letter, however it does not count double letters past
> column z. As in AA AB etc.
>
> Is there a way to get this?
>
> Ellen

#3
July 16th 07, 01:38 PM posted to microsoft.public.excel.worksheet.functions
 MartinW external usenet poster Posts: 860
CHAR funtcion with double letter columns

Hi Ellen,

Maybe a better way but this should work

HTH
Martin

"ellebelle" > wrote in message
...
>I am using this function:
>
> CHAR(MATCH(P\$1,\$1:\$1,)+64)
>
> to get teh column letter, however it does not count double letters past
> column z. As in AA AB etc.
>
> Is there a way to get this?
>
> Ellen

#4
July 16th 07, 01:44 PM posted to microsoft.public.excel.worksheet.functions
 ellebelle external usenet poster Posts: 34
CHAR funtcion with double letter columns

Thanks, but what is a UDF? where do I put this?

"Mike H" wrote:

> Try this UDF
>
> Function CLetter(CNumber As Integer) As String
> CLetter = Left(Cells(1, CNumber).Address _
> (False, False), 1 - (CNumber > 26))
> End Function
>
> call it with =cletter(n)
>
>
> Mike
> "ellebelle" wrote:
>
> > I am using this function:
> >
> > CHAR(MATCH(P\$1,\$1:\$1,)+64)
> >
> > to get teh column letter, however it does not count double letters past
> > column z. As in AA AB etc.
> >
> > Is there a way to get this?
> >
> > Ellen

#5
July 16th 07, 01:58 PM posted to microsoft.public.excel.worksheet.functions
 Mike H external usenet poster Posts: 11,501
CHAR funtcion with double letter columns

ellebelle,

Martin's solution does it wothout a UDF but for future reference it's a User
Defined Function.

To use it copy it and then Alt+F11.
Right click on 'this workbook' and insert module
Paste the code in there.

In any cell enter the formula =cletter(n)
where n is the number of the column you want.

Mike

"ellebelle" wrote:

> Thanks, but what is a UDF? where do I put this?
>
>
>
> "Mike H" wrote:
>
> > Try this UDF
> >
> > Function CLetter(CNumber As Integer) As String
> > CLetter = Left(Cells(1, CNumber).Address _
> > (False, False), 1 - (CNumber > 26))
> > End Function
> >
> > call it with =cletter(n)
> >
> >
> > Mike
> > "ellebelle" wrote:
> >
> > > I am using this function:
> > >
> > > CHAR(MATCH(P\$1,\$1:\$1,)+64)
> > >
> > > to get teh column letter, however it does not count double letters past
> > > column z. As in AA AB etc.
> > >
> > > Is there a way to get this?
> > >
> > > Ellen

#6
July 16th 07, 02:01 PM posted to microsoft.public.excel.worksheet.functions
 Chip Pearson external usenet poster Posts: 7,247
CHAR funtcion with double letter columns

> Function CLetter(CNumber As Integer) As String
> CLetter = Left(Cells(1, CNumber).Address _
> (False, False), 1 - (CNumber > 26))
> End Function

For compatibility with Excel 2007 which has 16384 columns (out to "XFD"),
use

Function CLetter(CNumber As Integer) As String
CLetter = Left(Cells(1, CNumber).Address _
(False, False), 1 - (CNumber > 26) - (CNumber > 702))
End Function

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

"Mike H" > wrote in message
...
> Try this UDF
>
> Function CLetter(CNumber As Integer) As String
> CLetter = Left(Cells(1, CNumber).Address _
> (False, False), 1 - (CNumber > 26))
> End Function
>
> call it with =cletter(n)
>
>
> Mike
> "ellebelle" wrote:
>
>> I am using this function:
>>
>> CHAR(MATCH(P\$1,\$1:\$1,)+64)
>>
>> to get teh column letter, however it does not count double letters past
>> column z. As in AA AB etc.
>>
>> Is there a way to get this?
>>
>> Ellen

#7
July 16th 07, 02:35 PM posted to microsoft.public.excel.worksheet.functions
 Harlan Grove[_2_] external usenet poster Posts: 1,230
CHAR funtcion with double letter columns

"MartinW" > wrote...
>Maybe a better way but this should work
>

....

Could be shorter.

#8
July 16th 07, 03:05 PM posted to microsoft.public.excel.worksheet.functions
 MartinW external usenet poster Posts: 860
CHAR funtcion with double letter columns

Hi Harlan,

I used to count lateral thinking as one of my talents.
These newsgroups soon knocked that nonsense out of my head. <g>

Regards
Martin

"Harlan Grove" > wrote in message
...
> "MartinW" > wrote...
>>Maybe a better way but this should work
>>

> ...
>
> Could be shorter.
>
>

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 Similar Threads Thread Thread Starter Forum Replies Last Post Double quotes when concatenating with CHAR(10) [email protected] Excel Discussion (Misc queries) 0 September 22nd 06 09:57 PM How do I format a letter in Excell with double strikethrough N? jt19373 Excel Discussion (Misc queries) 2 April 11th 06 12:49 AM 8500 cells with phone number(7 char.), wishing to add area code (10 char.) [email protected] Excel Discussion (Misc queries) 6 March 10th 06 05:13 PM Double Stacked Columns - can it be done? Mark Excel Discussion (Misc queries) 0 November 16th 05 10:18 PM How do I split 16 char text 'XX XXX= +. X' into 16 columns Jeff Excel Discussion (Misc queries) 9 August 15th 05 02:17 PM

All times are GMT +1. The time now is 07:18 AM.

 - Contact Us - ExcelBanter forum home - FAQ - Links - Privacy Statement - Top