Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



Vba - Converting A Column Number Into Column Letter

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I have over 2000 columns in my sheet - I am importing data from external binary random acces files - I bring these in as arrays of numbers - so how can I convert column 60 to a letter BH - column 186 to GD and so on.

View Answers     

Similar Excel Tutorials

Select Cells in Excel using Macros and VBA
This is actually a very easy thing to do and only requires a couple lines of code. Below I will show you how to sel ...
Make Column Headings Numbers instead of Letters - Make R1C1 Style References in Excel 2007
In Microsoft Excel you reference columns as letters by default - A1, B3, C5, etc. But you can also reference the co ...
Capitalize First Letter of Every Word in a Cell - PROPER Function
In Excel you can use a function to capitalize the first letter of every word in a cell.  This allows you to prepare ...
Vlookup with a Range of Numbers in Excel
How to use the Vlookup function to return a result that falls within a range of numbers, such as a weight or quanti ...

Helpful Excel Macros

Reverse Row or Column Order in a Worksheet
- This macro reverses the order of a selected row or column in excel. It will completely reverse the data.
Sort Data that Doesn't Have Headers in Descending Order in Excel
- Sort data in Excel that doesn't have headers using a macro. This Excel macro assumes that your data set does not have a
Generate a Non-Repeating List of Random Numbers in Excel - UDF
- Generate a series of non-repeating random numbers in Excel with this UDF (user defined function). This is a great funct
Vlookup Macro to Return All Matching Results and Stack them with Previous Results
- This is very similar to the other Vlookup type Macro in that it returns all of the results that match a particular se
Filter Data Sets on Multiple Columns with Multiple Criteria at Once in Excel - AutoFilter
- This Excel macro allows you to filter a data set on multiple columns and criteria at once. This means that you can filt

Similar Topics







Hi there,

In column "A" I have names. I am after a way to search that column for a certain letter and display a number corresponding to how many of that letter were found throughout column "A"

Maybe something like in column "B" rows 1-26 represent the corrisponding letter eg

B1 = all the a's as a number value from column "A"
B2 = all the b's as a number value from column "A"
etc etc

So if

A1 = Tim
A2 = Tom
A3 = Tina

Then

B1 = 1 (cause there is only one "a"

while

B9 = 2 (cause i=9th letter of alphabet and there are 2)

etc etc

Hope that makes sense.

Cheers

Tim


Hi there,

I've seen several posts about converting column numbers into column letters but I can't work out how to to it within my existing formula:

=SUMPRODUCT(($K$5:$CH$5="Yes")*(COLUMN($K$5:$CH$5)))

I'd rather convert it via formula than macro.

Any help greatly appreciated!


Hi there,

I'm trying to create a formula to get Excel 2003 to do basically this:

if the value of column i = 0 to 5 the letter value shown is L
if the value of column i - 6 to 11 the letter value shown is M
if the value of column i - 12 to 17 the letter value shown is H

basically i have columns F, G, H that have numerical values and a formula that adds those together to come up with a number 0 to 17. I want that number, based on the idea above, to visualize a letter instead of the number

for example:

F = 4
G = 4
H = 7
I = 15 so it would show up as H in the cell.

something like this:
=SUM(F12:H12) ----- THEN add the other part where if the answer is 0-5 it displays L, etc.

Thanks for any help and input!

Stephanie


I need to add one column into another..

i have a Column of numbers that all need to have a letter in front of them;
right now: 1, 10, 13, 14...etc
needs to be PS1, PS10, PS13, PS14..etc

The column does not go in numerical order so i cannot just drag the letter down. Is there a way to add these letter w/o having to copy and paste it into every row? Please help, i am trying to make a deadline!


What is the efficient way to convert a known column number to its corresponding letter - with acknowledgements to Aladin for the R1C1 formula...

Lastcol is the known column number, iStr a string variable
Code:

    Cells(1, LastCol).FormulaR1C1 = "=SUBSTITUTE(ADDRESS(1,COLUMN(),4),""1"","""")"
    iStr = Cells(1, LastCol).Value
    Cells(1, LastCol).ClearContents  'just to tidy up the sheet
    Columns("E" & iStr).Copy
    etc...


TIA GT




I need help with learning how to execute a command in Excel. It might involve the VLOOKUP function or macros or neither. I'm not sure.

I have 2 columns of 10 rows (for this example-- in reality there are 100+ rows)
Column A: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
Column B: a, b, c, c, c, c, d, e, f, f

When rows in Column B have matching data (like the 4 c's in B3, B4, B5, B6 or the 2 f's in B9, B10), I need the numbers in Column A in those rows (3,4,5,6 and 9,10) to change to a random number/letter and the two groups have to have a different number/letter from each other.
So 3,4,5,6 in Column A need to become X,X,X,X and 9,10 in Column A need to become Y,Y.

(In other words, any instance in which the data in a row in Column B matches other data in a row in Column B, the data in the corresponding rows in Column A need to change to match each other AND different groups of matching data in Column B need to produce different groups of matching data in Column A.)

From the original:
Column A: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
Column B: a, b, c, c, c, c, d, e, f, f

The result needs to be:
Column A: 1, 2, X, X, X, X, 7, 8, Y, Y
Column B: a, b, c, c, c, c, d, e, f, f

or:
Column A: 1, 2, D28, D28, D28, D28, 7, 8, D29, D29
Column B: a, b, c, c, c, c, d, e, f, f

Data in Column B doesn't change, and data in Column A ONLY changes if the data in the corresponding row in Column B matches another row in Column B. It really doesn't matter what the values in Column A change to as long as the groups are different from one another.

How do I accomplish this? Simplest way is best, but up for learning something more complex if it's the only way.

Thanks Excel gurus!

I have a huge list of car models inventory. About 15,000+ entries in an Excel spreadsheet. I am trying to find out the first letter of the name of the car.

I have the following four formats:
Mercedes CLS 500 (i.e., Name begins with a letter)
2000 Chevrolet Corvette (i.e., 4 numbers + space then the letter)
4x4 Jeep (i.e., number + x + number + space then the letter)
8-Wheel Tipper Truck (i.e., number plus a dash then the letter

It is very easy if the first character is a letter. I used the "Left(a1,1)" command to find the first letter. However, I am not sure how to find the first letter if the first character is a number.

Is there any easier way of find this without going through VBA?

Any help is greatly appreciated.


Ultimate goal: Insert a page break at each blank row. I have the macro to insert page breaks already.

I run a report several times a day that needs to be printed and handed out to different people for specific tasks to be taken care of. The issue is that I get the data in one lump and I have to manually insert page breaks each time. There are a large number of 'Locations' that are grouped together to be handed to different people. The bulk of this can be separated just by the first letter of each location. Below is an example of what my data looks like, it only needs to insert a row based on what's in column A.

Code:

                                                                                                                                                                                                                                                                                                                                                                           
   


 
Original data
 


   


 


 


   
A-A-0
 
random data
 
random data
   
A-A-1
 
random data
 
random data
   
A-A-2
 
random data
 
random data
   
A-B-0
 
random data
 
random data
   
A-B-1
 
random data
 
random data
   
A-B-2
 
random data
 
random data
   
A-C-0
 
random data
 
random data
   
A-C-1
 
random data
 
random data
   
A-C-2
 
random data
 
random data
   
B-A-0
 
random data
 
random data
   
B-A-1
 
random data
 
random data
   
B-A-2
 
random data
 
random data
   
B-B-0
 
random data
 
random data
   
B-B-1
 
random data
 
random data
   
B-B-2
 
random data
 
random data
   
B-C-0
 
random data
 
random data
   
B-C-1
 
random data
 
random data
   
B-C-2
 
random data
 
random data
   
B-D-0
 
random data
 
random data
   
B-D-1
 
random data
 
random data
   
B-D-2
 
random data
 
random data
   
B-E-0
 
random data
 
random data
   
B-E-1
 
random data
 
random data
   
B-E-2
 
random data
 
random data
   
B-F-0
 
random data
 
random data
   
B-F-1
 
random data
 
random data
   
B-F-2
 
random data
 
random data
   
C-A-0
 
random data
 
random data
   
C-A-1
 
random data
 
random data
   
C-A-2
 
random data
 
random data
   
C-B-0
 
random data
 
random data
   
C-B-1
 
random data
 
random data
   
C-B-2
 
random data
 
random data
   
C-C-0
 
random data
 
random data
   
C-C-1
 
random data
 
random data
   
C-C-2
 
random data
 
random data
   
C-D-0
 
random data
 
random data
   
C-D-1
 
random data
 
random data
   
C-D-2
 
random data
 
random data
   
C-E-0
 
random data
 
random data
   
C-E-1
 
random data
 
random data
   
C-E-2
 
random data
 
random data
   
C-F-0
 
random data
 
random data
  



Code:

  
         


   
Needs to look like
   


       


   


   


       
A-A-0
   
random data
   
random data
       
A-A-1
   
random data
   
random data
       
A-A-2
   
random data
   
random data
       
A-B-0
   
random data
   
random data
       
A-B-1
   
random data
   
random data
       
A-B-2
   
random data
   
random data
       
A-C-0
   
random data
   
random data
       
A-C-1
   
random data
   
random data
       
A-C-2
   
random data
   
random data
       


   


   


       
B-A-0
   
random data
   
random data
       
B-A-1
   
random data
   
random data
       
B-A-2
   
random data
   
random data
       
B-B-0
   
random data
   
random data
       
B-B-1
   
random data
   
random data
       
B-B-2
   
random data
   
random data
       
B-C-0
   
random data
   
random data
       
B-C-1
   
random data
   
random data
       
B-C-2
   
random data
   
random data
       
B-D-0
   
random data
   
random data
       
B-D-1
   
random data
   
random data
       
B-D-2
   
random data
   
random data
       
B-E-0
   
random data
   
random data
       
B-E-1
   
random data
   
random data
       
B-E-2
   
random data
   
random data
       
B-F-0
   
random data
   
random data
       
B-F-1
   
random data
   
random data
       
B-F-2
   
random data
   
random data
       


   


   


       
C-A-0
   
random data
   
random data
       
C-A-1
   
random data
   
random data
       
C-A-2
   
random data
   
random data
   






I have a chart with the alpabet in the first column in the next four columns are numbers that correspond to the letters in the 1st column. How can I use MS Word to reference my chart and convert these numbers to letter and vise versa. Also since I have 4 seperate columns of numbers. I need to be able to specify which column of numbers I want to be used to deciper my alphbet. Does anyone have any ideas on the best way to do this? Basically it is making 4 seperate decoder wheels/rings but just automated so it will decipher what is typed in Word (alpha or numeric). Thanks for you help.


Hi,

I need help with either a Conditional format of a cell or a vba code that does the same.

I want a cell to reject an entry if, the following format is not met

letter letter number space number letter letter

i would like it to have variables of,

letter letter number number space number letter letter

I have found a VBA code to match the following,

letter letter number number number number number number letter.

that has worked perfectly but I would like to solve the format highlighted in red above.

Due to the operating size of the file and its linking data to 130 other files i am tryng where possible not to have mulitiple formula's if functions etc across to solve this one riddle.

urgent help most welcome


Is it possible to subtract a number from a column letter?
I'm trying to create a sheet that does some helpful calculations. In order for me to finish it without a macro i need to be able to subtract a number in a cell from a column letter.
Example F-5=A, The end i'm trying to achieve is to get a sum range to move left and right in this data set based on that number.

Any ideas?

Thanks All,

Chris


I used the MATCH function to look up a column header - is there a way to convert that to the corresponding column letter? So the result of my formula is "8" and I want to be able to have that convert to "H".

I'm trying to use an INDIRECT function elsewhere and I need that letter reference to make up the appropriate cell reference.

Help is appreciated!!


Is there an easy way (without writing my own conversion function) to get the column letter from a range object, as opposed to the column number?


Hi guys
Am new to excel forums so please forgive any inaccuracies in my description of things.
Here goes
have four worksheets in same workbook,only two of which are relevant here,

Named,
Letter and Certificates

Letter sheet has two pages, page 1 contains letter, page 2, contains Info sheet, as entire letter is written in (column B)
this is where I need the info to appear.

Certificates Sheet contains student names and course future expiry dates
all this is over 9 columns, 5,of which, ( C:G, rows 2:1000) I need to pull the info from,

EG.row c2/d2/e2/f2/g2,and drop into column B letter

column, G, contain dates, and has conditional formatting on future dates, EG. 30 days before date expires.

what I need is 30 days before date in column G in certificates sheet expires data from columns C:G to appear In,
Letter sheet column B, rows 64:100

hope I`ve explained this properly

Is any of this possible?
Thank you all


Is there a function that will output the column letter? For example there's one I know of: =COLUMN(), which outputs column number, but not the letter. And if not, can a formula be written to output it without converting the spreadsheet to R1C1 style or using the lookup function that refers to a separate table within the spreadsheet?

Thanks


Dear forum users,

There is a problem I wonder if you could help me with. Perhaps it is a simple matter but I have been unable to find the answer.

Large numbers are commonly abbreviated with binary prefixes, especially in finance. By binary prefix, I am referring to the use of a single letter to represent the order of magnitude of a number. For instance "K" represents a thousand, and "M" denotes a million. e.g. The population of the US is around 300M (or 0.3B).

So when I have a column of numbers such as "... 8000; 9000; 10K; 11K...", I want Excel to convert or recognise the value of "K". At the moment, Excel is just treating this as text. Does anyone know how to do this?

Thank you in advance for your help.


1. Is there a better way of converting a column number to its letter equivalent than this:

Code:

colName = split(columns(colNumber).address,"$")(2)





I have two columns one of which contains a number and the other a letter. The letter is related to the number.
Now I have a third column that contains the same numbers as is column 1, but they are slightly different in order. I want the new column to have appropriate letters behind them despite the new order.
I have tried =if and =lookup but haven't succeeded.


Hello,

I have the addresses of some cells written down in this format:

$G$12
$K$17
$Z$23
$P$28

i am looking for a custom function to convert the column Letters to numbers starting with letter "A" being number 1 etc... i.e. for the first example - if my custom function was used on $G$12, then I would like it to return the number 7, because G is the seventh letter in the alphabet.

is this possible??

thanks
andy


I have several hundred multiple choice questions. I can convert them to any way I choose in Word, Excel, or as a text/csv file. The order is not important.

My goal is to choose a random 100 questions from that pool and have it either copied to a new sheet/document or whatever.

My questions can start with a number: 1.), 1. , or without a number. Remember, I'm flexible with how they can look and in what format we can use.

My choices can start with a letter: a.), a. , or without a letter.

Right now, my questions start with "1. " and my choices start with "a. ". They are only populated in Column A and go straight down as such:

Column A
1. Question 1
a. Choice 1
b. Choice 2
c. Choice 3
d. Choice 4
Blank Row
2. Question 2....

What I hypothesize would work would be to associate a random number from 1-100 for my entire pool of questions. The questions that do get a number between 1-100 get to be chosen.

Do any of you have any ideas on what I can do to accomplish this goal?

Ken




I have column Z that has one letter in it. The letter will be either A, B, C or D.

If the letter A in in Z1, then I want column AA to be Apple.

I've got a separate excel tab that has the values in A1-5 and B1-5
A, Apple
B, Banana
C, Carrot
D, Doughnut

How to I use an "if" statement that will fill in column AA to put the word that matches the letter in the column Z1

Thanks!

I have 6 columns (C2:H11). Each list has 10 rows of text items. For this example let's use the first 15 letters of the alphabet (a THRU o). They are in each list random however any one letter can be in 1 list to all the lists.

What I need is a way to make a unique list in column J of all the letters appearing in all the lists, obviously with no duplicates.

Finally in Column K I would like the total number of times each letter in column J appears in all 6 columns.

I have tried a few different formulas but I cannot get anything to work. Can someone help me out here?

Thanks
Zen


Would like to Convert a Letters to numbers and total numbers in cell. Example (A1,A2,A3,=letter V), convert to numbers 1's and total in A4=numbers 3. I'm working on time sheet for work. thanks


Hi all,

I have column B which comprises of month and date : Jan 02-25. However, some of them are in small letters, eg : jan 02-25. That makes the sorting inaccurate. How to convert every first letter of the month in column B to capital letter?

Thank you so much for all the help that you have given! Indeed, Mr.Excel has helped me so much that I just want to say a big thank you!

m111


G'day Everyone,

I am wanting clients to submit a letter in a column & have it represented by a number in another column!

Example:
B1 ( Client needs to type Y or N ) Letter values (Y=100 & N = 50)
C1 = answer

Is this a LOOKUP, IF or some other formula I should be looking at!

Thanks in advance to all whom reply!

Chad-NextByte