Vba  Converting A Column Number Into Column Letter 


Vba  Converting A Column Number Into Column Letter  Excel 
View Answers 
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.
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 ...
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 ...
In Microsoft Excel you reference columns as letters by default  A1, B3, C5, etc. But you can also reference the co ...
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 ...
How to use the Vlookup function to return a result that falls within a range of numbers, such as a weight or quanti ...
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 ...
In Excel you can use a function to capitalize the first letter of every word in a cell. This allows you to prepare ...
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.
 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
 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 NonRepeating List of Random Numbers in Excel  UDF
 Generate a series of nonrepeating random numbers in Excel with this UDF (user defined function). This is a great funct
 Generate a series of nonrepeating 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
 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
 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 126 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
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 126 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!
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 05 it displays L, etc.
Thanks for any help and input!
Stephanie
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 05 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!
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:
TIA GT
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)
8Wheel 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.
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)
8Wheel 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:
Code:
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 AA0 random data random data AA1 random data random data AA2 random data random data AB0 random data random data AB1 random data random data AB2 random data random data AC0 random data random data AC1 random data random data AC2 random data random data BA0 random data random data BA1 random data random data BA2 random data random data BB0 random data random data BB1 random data random data BB2 random data random data BC0 random data random data BC1 random data random data BC2 random data random data BD0 random data random data BD1 random data random data BD2 random data random data BE0 random data random data BE1 random data random data BE2 random data random data BF0 random data random data BF1 random data random data BF2 random data random data CA0 random data random data CA1 random data random data CA2 random data random data CB0 random data random data CB1 random data random data CB2 random data random data CC0 random data random data CC1 random data random data CC2 random data random data CD0 random data random data CD1 random data random data CD2 random data random data CE0 random data random data CE1 random data random data CE2 random data random data CF0 random data random data
Code:
Needs to look like AA0 random data random data AA1 random data random data AA2 random data random data AB0 random data random data AB1 random data random data AB2 random data random data AC0 random data random data AC1 random data random data AC2 random data random data BA0 random data random data BA1 random data random data BA2 random data random data BB0 random data random data BB1 random data random data BB2 random data random data BC0 random data random data BC1 random data random data BC2 random data random data BD0 random data random data BD1 random data random data BD2 random data random data BE0 random data random data BE1 random data random data BE2 random data random data BF0 random data random data BF1 random data random data BF2 random data random data CA0 random data random data CA1 random data random data CA2 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
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 F5=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'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 F5=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!!
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
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
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.
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:
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.
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 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 1100 for my entire pool of questions. The questions that do get a number between 1100 get to be chosen.
Do any of you have any ideas on what I can do to accomplish this goal?
Ken
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 1100 for my entire pool of questions. The questions that do get a number between 1100 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 A15 and B15
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
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 0225. However, some of them are in small letters, eg : jan 0225. 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
I have column B which comprises of month and date : Jan 0225. However, some of them are in small letters, eg : jan 0225. 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!
ChadNextByte
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!
ChadNextByte