A Microsoft Excel forum. ExcelBanter

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.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
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  
Old July 16th 07, 01:12 PM posted to microsoft.public.excel.worksheet.functions
ellebelle
external usenet poster
 
Posts: 34
Default 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
Ads
  #2  
Old July 16th 07, 01:38 PM posted to microsoft.public.excel.worksheet.functions
Mike H
external usenet poster
 
Posts: 11,504
Default 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  
Old July 16th 07, 01:38 PM posted to microsoft.public.excel.worksheet.functions
MartinW
external usenet poster
 
Posts: 860
Default CHAR funtcion with double letter columns

Hi Ellen,

Maybe a better way but this should work

=LEFT(ADDRESS(1,COLUMN(),4),LEN(ADDRESS(1,COLUMN() ,4))-1)

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  
Old July 16th 07, 01:44 PM posted to microsoft.public.excel.worksheet.functions
ellebelle
external usenet poster
 
Posts: 34
Default 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  
Old July 16th 07, 01:58 PM posted to microsoft.public.excel.worksheet.functions
Mike H
external usenet poster
 
Posts: 11,504
Default 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  
Old July 16th 07, 02:01 PM posted to microsoft.public.excel.worksheet.functions
Chip Pearson
external usenet poster
 
Posts: 7,247
Default 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  
Old July 16th 07, 02:35 PM posted to microsoft.public.excel.worksheet.functions
Harlan Grove[_2_]
external usenet poster
 
Posts: 1,230
Default CHAR funtcion with double letter columns

"MartinW" > wrote...
>Maybe a better way but this should work
>
>=LEFT(ADDRESS(1,COLUMN(),4),LEN(ADDRESS(1,COLUMN( ),4))-1)

....

Could be shorter.

=SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")


  #8  
Old July 16th 07, 03:05 PM posted to microsoft.public.excel.worksheet.functions
MartinW
external usenet poster
 
Posts: 860
Default 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
>>
>>=LEFT(ADDRESS(1,COLUMN(),4),LEN(ADDRESS(1,COLUMN (),4))-1)

> ...
>
> Could be shorter.
>
> =SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")
>



 




Thread Tools
Display Modes

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

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 01:57 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.