Vba - Converting A Column Number Into Column Letter

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 right-most column for each letter. I want the cell next to the right-most 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
 


   


 


 


   
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
   

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)
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.

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 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