
Vba  Converting A Column Number Into Column Letter


Search Excel Forum Posts, Tutorials, Macros, Tips, and More
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 Video Tutorials
Similar Topics
I have six columns, Column A titled "Cooperation", Column B titled "Work Habits", and Column C titled "Total". Column D titled "Letter Grade", Column E titled "Cooperation Grade", and Column F titled "Work Habits Grade". I want the number that is entered into columns A, B, and C to automatically enter a corresponding letter grade into columns D, E, and F. Column A corresponds with column E, Column B with column F, and column C with column D.
If the number in column A is less than or equal to 2 i need the letter "E" to appear in column E, if the number is 34 then the letter "S" should appear, if the number is 5 or higher the letter "U" should appear.
If the number in column B is less than or equal to 2 i need the letter "E" to appear in column F, if the number is 34 then the letter "S" should appear, if the number is 5 or higher the letter "U" should appear.
If the number in column C is less than or equal to 3 i need the letter "A" to appear in column D, if the number is 46 then the letter "B" should appear, if the number is 78 the letter "C" should appear, if the number is 910 the letter "D" should appear, if the number is greater than 10 the letter "F" should appear.
I hope this makes sense. Thanks for looking
I just know this one is going to be obvious...
how do I convert a column number to its equivalent letter heading?
I am moving data from one sheet to another and manipulating the arrays using variables like row_num & col_num, but want to convert the col_num to the letter heading.
i.e. column 93 is "CO"
I could probably write a sub to do it but surely this exists already?
Ian
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
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 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!
Hi!
I want to convert the following:
8 to A*
7 to A
6 to B
5 to C
I have a column of pupil results in numbers but need to convert them a Grade. Can anyone help?
Many thanks,
Russ
Here's the gist of what i'm trying to do.
1 A 1
1 A 2
3 A 5
15 B 15
9 C 9
12 D 12
6 D 18
12 D 30
10 D 40
As you can see, the first column is a number that's data for the letter to the right. To the right of the letter, is a running sum of the data in the first column, which only sums if the letters match. What i'm trying to do, is figure out a way to get the max number from the rightmost column for each letter. I want the cell next to the rightmost number to be blank, unless that number is the max for the letter. I hope that isn't too confusing ...and doable. Thanks in advance for any help!
Hi
How can I get the "Letter" of the column instead of the number of the column?
For intance, when using:
LastColumn = ActiveSheet.UsedRange.Column.Count it returns the Number of the last column with data instead of the column letter.
Thanks
Ed.
i have a list of data, only two columns, column a contains a series of 9 numbers whilst column b contains just one letter. what i need to do is tag the letter in column b onto the end of the number in column a.
Any ideas?
This site has always been very helpful so any assistance would be appreciated.
I have look for this on google for almost a week . And I have found nothing.
I'm Importing Columns from one sheet to another in the same workbook. imports good But:
1 Some columns with first records/rows blank do not import at all.
2 I have a column with some letter/numbers combinations and single numbers at times. Those single numbers are not imported. And half my data in that column are single numbers.
Can anyone help me please ?
Hi Guys,
I need you're assistance for the following;
I am trying to devise a spreadsheet in which I need to enter a letter from a drop box into a cell then this letter to convert to a number in another cell to aid calculation.
I.e. cell a1 = M to convert to the figure 8 in cell b1 or if another letter is selected i.e. cell a1 = A to convert to the figure 9.5 in cell b1.
If this is possible I would emphasise this has to be from a validation dropbox.
As always  appreciate whatever help you care to give.
Thanks = dustyv
I need to do the following:
1. Select a cell in column I
2. Press f12>input box asks for two letter code
Loop
3 User enters a two letter code>excel generates a random number between 1 and 100
4 Excel appends the inputted two letter code to the random integer
eg random integer=57 and inputted code is "CM"
>result is CM57
5 Excel searches all of column I for CM57
.6 If found, then loop back to step 3
Until the result is NOT found in column I
Populate current cell with the result ie CM57
.
Hi there,
I'm new to the group as you've probably guessed, I'm a little stuck and have trawled all over the web seeking an answer to this problem, to now i've found nothing, I thought you guys may be able to help.
Say I have the following in excel:
Column 1 I Column 2 I Column 3
I I
One I Number I
A I Letter I
One I Number I
A I Letter I
One I Number I
Is there a way to make Excel say "okay there are three One's which are numbers S i'm going to delete 2 of them and them put 3 in Column 3, there's also 2 A's so I'll do the same leaving:"
Column 1 I Column 2 I Column 3
I I
One I Number I 3
A I Letter I 2
Any help that could be offered would be really appreciated.
Many thanks
Erm, it keeps taking my spaces out so it doesn't look more spreadsheety.
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
ok
Columns
A .... B ................ C ...... E .. F .. G .... H
A....Miami.........17......A..B..C...40
B....Detroit........06.....E..A..D...27
C....Dallas.........17.....C..E..B...33
D....Tampa Bay..00.....D..A..B...23
E....Kansas City..10.....A..C..E...44
What I am trying to get is a formula in Column H,to add the values of Column E,F & G, in each column of course the Letters A,B,C,D & E represent the numbers in column c, Letter A=17,Letter B=6,Letter C=17,etc ,Im not that savvy in excel,but Im sure Im missing something minor to get this to work,I can get it to work for one column but not all 3,any suggestions
Thanks
Steve
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
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
Is there a coresponding formula that column numbers can be used instead of column letters?
Columns("O:R").Select
If not what is the best way to get the column letter if I know the column number?
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.
Hi,
Basically, I need to be able to determine whether a particular letter is stored with another letter in the 'CG column' of the attached spreadheet.
The criteria is this:
1. Letter J can be present as long as there is no other letter in that column.
2. Letter B and D cannot be together in the column, but B can be with any other letter and likewise for D.
Basically, I could have a load of Js in the CG column and that would be fine. If another letter was with those Js, I need a warning box. I could have a B,H,S,T etc.....and that would be fine, but as soon as a D is entered, I need a warning. Similarly, if I had D,H,I,U,T etc......that would be fine too, but as soon as a B is entered, I need a warning again.
Sorry if I am making this sound really long winded, but I just wanted to try and make it as clear as possible. Any help or guidance would really be appreciated.
Regards,
Mark
I would like to be able to read and interpret and convert the data in columns E, F, & G and create colum H using the interpreted data. I have included a sample table, the "correct" interpretations, the constants and the "method" of interpretation. I would greatly appreciate any help as I have to do this daily and for many processors...Thanks in advance! cliff
CONSTANTS:
COLUMN E = SEX
SEX:
M = M
F = F
COLUMN F = ED CODE
ED CODES:
109 = C
1EE = N
111 = N
11E = H
11S = S
12 or greater and a letter or third number = G
(i.e. 14L = G)
(i.e. 146 = G)
COLUMN G = AFQT
0 = T
1  30 = 4
31  49 = B
50  100 = A
COLUMN H = CAT
(CORRECT RESULT OF INTERPRETATION)
FIRST LETTER OF CORRECT RESULT:
comes from COLUMN F (Ed code)
SECOND LETTER OF CORRECT RESULT:
comes from COLUMN E (Sex)
THIRD LETTER OF CORRECT RESULT
comes from COLUMN G (AFQT)
need to "read" COLUMNS E, F, & G
interpret the columns using the constants above
and place the results into column H
would prefer
1. using columns as constants
2. start macro on cell D2
3. stop macro on first empty cell in Column A
SEX
ED CODE
AFQT
CAT
CORRECT
RESULT
M
12L
0
GMT
M
12L
46
GMA
M
12L
97
GMA
M
12L
36
GMB
M
12L
57
GMA
M
11S
66
SMA
F
12L
75
GFA
M
12L
41
GMB
M
12L
54
GMA
M
109
49
CMB
M
109
33
CMB
M
109
87
CMA
M
111
38
NMA
M
11S
34
SMB
M
12L
78
GMA
M
12J
61
GMA
M
16K
95
GMA
F
109
74
CFA
M
109
61
CMA
M
109
62
CMA
M
12L
75
GMA
M
109
56
CMA
M
11S
34
SMB
M
109
70
CMA
M
109
43
CMB
F
109
54
CFA
M
109
84
CMA
M
12J
60
GMA
M
109
48
CMB
M
109
53
CMA
M
109
32
CMB
M
109
45
CMB
M
12J
35
GMB
M
109
31
CMB
F
11S
55
SFA
M
109
32
CMB
M
12L
38
GMB
M
14L
53
GMA
M
12L
0
GMT
F
12L
48
GFB
F
12L
82
GFA
M
12L
46
GMB
M
146
78
GMA
I would like to be able to read and interpret and convert the data in columns E, F, & G and create colum H using the interpreted data. I have included a sample table, the "correct" interpretations, the constants and the "method" of interpretation. I would greatly appreciate any help as I have to do this daily and for many processors...Thanks in advance! cliff
CONSTANTS:
COLUMN E = SEX
SEX:
M = M
F = F
COLUMN F = ED CODE
ED CODES:
109 = C
1EE = N
111 = N
11E = H
11S = S
12 or greater and a letter or third number = G
(i.e. 14L = G)
(i.e. 146 = G)
COLUMN G = AFQT
0 = T
1  30 = 4
31  49 = B
50  100 = A
COLUMN H = CAT
(CORRECT RESULT OF INTERPRETATION)
FIRST LETTER OF CORRECT RESULT:
comes from COLUMN F (Ed code)
SECOND LETTER OF CORRECT RESULT:
comes from COLUMN E (Sex)
THIRD LETTER OF CORRECT RESULT
comes from COLUMN G (AFQT)
need to "read" COLUMNS E, F, & G
interpret the columns using the constants above
and place the results into column H
would prefer
1. using columns as constants
2. start macro on cell D2
3. stop macro on first empty cell in Column A
SEX
ED CODE
AFQT
CAT
CORRECT
RESULT
M
12L
0
GMT
M
12L
46
GMA
M
12L
97
GMA
M
12L
36
GMB
M
12L
57
GMA
M
11S
66
SMA
F
12L
75
GFA
M
12L
41
GMB
M
12L
54
GMA
M
109
49
CMB
M
109
33
CMB
M
109
87
CMA
M
111
38
NMA
M
11S
34
SMB
M
12L
78
GMA
M
12J
61
GMA
M
16K
95
GMA
F
109
74
CFA
M
109
61
CMA
M
109
62
CMA
M
12L
75
GMA
M
109
56
CMA
M
11S
34
SMB
M
109
70
CMA
M
109
43
CMB
F
109
54
CFA
M
109
84
CMA
M
12J
60
GMA
M
109
48
CMB
M
109
53
CMA
M
109
32
CMB
M
109
45
CMB
M
12J
35
GMB
M
109
31
CMB
F
11S
55
SFA
M
109
32
CMB
M
12L
38
GMB
M
14L
53
GMA
M
12L
0
GMT
F
12L
48
GFB
F
12L
82
GFA
M
12L
46
GMB
M
146
78
GMA
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
Would this be possible to do as a formula??
In a column, if A1 contains the letter 'a' and A10 contains the letter 'a' also, then fill all the cells in between A1 and A10 with the letter 'a' thus forming a column of 10 cells containing the letter 'a' ???? The formula I guess would need to know that there is an 'a' above and an 'a' below so that it can fill all the cells in between with the letter 'a'.
Basically, there will be many columns with the same letter at the top and bottom, thus i would need the cells in between the top and bottom letter filled with the same letter in the column to form something like the following:
a
abc
abc
abc
abc
ab
ab
ab
a
a
Replies would be much appreciated.
Thanks.
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

