# Turn Column Letters To Numbers And Vice Versa?

Jun 16, 2014
How do i turn column letters to numbers and vice versa

take y values from column and take x values from row

I have 'resolved' values in column A1:A10

I have 'received' values in row B11:K11

I need to fill out a table using the tables axis values stored in the column and row above.

View 3 Replies
ADVERTISEMENT
Dec 11, 2006

Let's say that row a,b,and c contain a list price, discount %, and discount price respectively. I want to be able to change either the discount % and it will recalculate the discount price or change the discount price and it will recalculate the discount %. So to put it more clearly:

cells in row A: Contain the List (undiscounted) price. This will never change.

cells in row B: Will be a discount %. It is equal to:

(list price - discounted price)/list price. needs to be recalculated if discounted price changes. Also, it should only contain data if the cell in Row A - list price - contains data.

All cells in row C: Will be a discount price. It is equal to:

(1-discount %)*list price. needs to be recalculated if discount % changes. Also, it should only contain data if the cell in Row A - list price - contains data.

View 9 Replies
View Related
Apr 9, 2014

I am looking for a code that will be able to link cells H9:I14 on Sheet 1 with cells H7:I12 on Sheet 2 of the same workbook so that if I change H9 on Sheet 1, H7 on Sheet 2 will show the same figure and alternatively if I change H7 on Sheet 2, H9 on Sheet 1 will show the same figure. If this could work for all 12 of the cells and their equivalents respectively.

Furthermore, If a blank column or row is inserted, hence the cells move, the link will remain useable.

I have plenty of different columns throughout the workbook where this needs to be done so I imagine I can just adjust the code as necessary to incorporate different cells.

View 3 Replies
View Related
Feb 12, 2007

How can I separate a text or a number. For example in column A I have a data written like these 123text, text1234, 123text123, 123-text and in column B I only want to put the text or the number only so it means that if I have in column A "123text" in column B I only want to put "text" word. Another information is that the number is not always 3 number and the text is not always 4 character.

View 9 Replies
View Related
Aug 22, 2008

I need to have a formula or code so that when a number is entered in cell E12 or F12 or L12, or M12 would treat a positive number as a negative and a negative number entered would be a positive in that respective cell.

View 9 Replies
View Related
Jun 3, 2009

I've attached a sample/equivalent workbook of what I'm working on which will hopefully make it clear(er).

>There are two worksheets/month. Both worksheets (represent 2 different categories) are structured the same, two columns: model code & $ amount. >The model codes change (in # and actual model), between categories and month.

>The data for each month rolls up into a year-to-date summary worksheet, with 4 columns: Model (includes all models YTD, each only listed once), category1 YTD, category 2 YTD, & Total YTD).

Previously this had been done by manually entering any new models for the month into the rows in the YTD summary sheet. And the totals for each model (highlighted in yellow in the YTD tab in my sample) were just done by an adding formula, with the new month's data manually entered into each individual cell at the end of the formula (...+X). I know there's a much better way to do/automate this! (there are a lot more models than I've put in my sample aka it's way too time consuming manually).

My problem is twofold:

1. (main issue) I have been trying to do this using various IF statements nested in VLOOKUPS, and vice versa, but the issue that arises is for models in the summary sheet that don't exist in a given (month's) table. I want the value for those models (for that specific month) to be zero, but I cannot figure out how to get that to work in my formula. The only piece that works for me thus far is =VLOOKUP(A3, 'Jan Cat1'!A2:B18, 2, FALSE), but I've tried nesting it in IF statements, nesting IF statements in it, using ANDs & ORs, no avail.

I'm not even sure any of these options are the best ways to reach what I'm ultimately trying to do. A pivot table may be better? But I will need to keep/preserve the summary sheet for each month (so there cannot just be one big updated master pivot table).

2. If I could find a way to automate/refresh & update the row of models each month, it would be the sprinkles on the icing of this cupcake.

View 10 Replies
View Related
Jul 1, 2008

I have a column of numbers such as

1001150

1001124

2224445

I need add a period in the following locations

10011.50

10011.24

22244.45

I figured this out using a format rule of

#.##

I then need to make the numbers negative so I did

-#.##

but this doesn't "stick", if I filter the numbers by negative numbers, none of them show up. So how do I make the formatting actually become the numbers? Auto Merged Post Until 24 Hrs Passes;After doing some more research I found the "precision as displayed" option. I can't find this option on Excel 2007, but I moved the files into 2003 and the option doesn't do anything. It is not permanently changing the column that I have added the formatting too.

View 4 Replies
View Related
Dec 13, 2011

VLookups can be used to change letters into numbers, is it possible to turn numbers into letters?

i.e

A-7 =vlookup(A,table1,2,1)

7-A =????

View 3 Replies
View Related
Feb 5, 2010

Attached is the sample data worksheet. Chart 1 is XY type chart using Seconds (2nd column of sample sheet as x-axis from 42510 to 42530). How do I change it to Line chart using Time (1st column of sample sheet as the X-axis) retaining same data from 42510 to 42530 on both primary and secondary axis?. And how do I again change it back to XY chart?

View 3 Replies
View Related
Jul 24, 2012

I have a column of cells, some blank, some containing just numbers, some containing just letters, some containing numbers preceded by the the letter 'p'

E.g.

frt

34.2

36

p34.5

In the cells containing the number preceded by the 'p' - i would like to remove the 'p' leaving just the number, with all other cells remaining unchanged.

View 3 Replies
View Related
Jun 24, 2014

how to create some code that can convert any column number to it's respective letter, from 1 = "A" to 16384 = "XFD". The code is posted below. Enjoy, or let me know how you would improve it.

Code:

Function colNumToChar(colNum) As String

Dim Num1, Num2, Num3 As Integer

Dim Let1, Let2, Let3 As String

[Code]....

View 9 Replies
View Related
Aug 16, 2006

All my columns headings are now numbers instead of letters. how do i change them back to letters

View 5 Replies
View Related
Jan 9, 2007

I have a excell spreadsheet that consolidate information from several other excel spreadsheets generated daily. To make this consolidation I have to open each daily excell file and copy some columns(always same ones) to my consolidate spreadsheet, but each daily information goes into a different column in the consolidated file.

So, I need a way to convert the columns names into numbers to be able to use loops on the VBA code where I can specify in each column the paste has to be done.

Anyone has the VBA code that can make this column name conversion into numbers?

View 2 Replies
View Related
Feb 2, 2007

I want to be able to use column numbers instaed of letters when selecting a range in VBA. My current code is: Range("G12:Z500").Select. The columns, however are reliant upon a variable, therefore I would like to be able to use that variable to change the columns when selecting this range.

View 2 Replies
View Related
Jul 30, 2014

how can I modify the formula below so that after every row (i+60) the letter D changes to E then F, then G..... and so on. I want the following with the formula below:

=MAX($D$2:$D$61)

=MAX($E$62:$E$121)

=MAX($F$122:$F$181)

and so on...

[Code] .....

View 8 Replies
View Related
Aug 12, 2007

I am attempting to sort a column with mixed letters and numbers. However, I have been totally unable to get them to sort the way I want them.

What I have is:

K600

K2

K2A

K2B

K159

K159A

K159B

K1

K1A

K1B

K428

K8

K8A

K8B

etc, etc. The prefix letter is only a K - no other letters. However, the suffix letters can be anything from A to E (at the present time)

I need to sort them numberically from K1 in descending order ie:

K1

K1A

K1B

K2

K3

K4

K4A

K4B

etc etc etc

View 9 Replies
View Related
Jun 16, 2014

I have a row of numbers that change as they go up. I will have 2 rows of numbers that will range from 1's all the way to 9's (B through BO) I will have different letter sequences that I will be entering as the criteria in column F either on the Up and Down sheet or the Even and Odd sheet. When I say different sequences. This is 1 group of letters. The next group of letters in column F will be in a different order. They will always be a U and D in one and E and O in the other but in row 13 it may have a U or D and it will be the same all the way down. The object is for me to be able to place the letters in either column F from rows 13 to 60, place 2 rows of numbers in rows 58 and 59, press the advance cell in G1 and it it populates all across the spreadsheet from I to BO. I will get all different number sequences since I am starting with a different number in rows 58 and 59. I have all the letters color coded and they move up based upon the color of the letters in row 1. If there is a green E in column M then I will be the column that the number advances from 1111 to 2111. If there is a green O then column K will advance up from 1111 to 1121.

I made a short video if you wish to view it.

The conditional formatting is very important so I need to have the green font with gray shade and red font with yellow shade [URL]....

View 2 Replies
View Related
Jul 29, 2014

i have a list of 2000 fields which have the same format IE "AB10014"

I need to remove the "AB" from every field and leave the #.

Besides putting a space and running text to columns I'm not sure how.

View 13 Replies
View Related
Jan 21, 2012

Using the following data

R10-12128

R11-12x12x8

R11-12x12x8

R1-12x12x8

R1-12x12x8

R12-12x12x8

R14-12x12x8

R16-12x12x8

R18-12x12x8

R2-12x12x8

I want it to sort like this:

R1-12x12x8

R1-12x12x8

R2-12x12x8

R10-12128

R11-12x12x8

R11-12x12x8

R12-12x12x8

R14-12x12x8

R16-12x12x8

R18-12x12x8

What is the formula to achieve this?

View 5 Replies
View Related
Nov 17, 2009

Is there a VBA command to get the letters, instead of the numbers, of the column of a selected cell?

I have to letter a list whihc means setting up a loop using character codes.

I may have to go into double letters so I am working on how I would set up the loop for if and when it gets past 90 and starts on double letters. so far the highest is the letter "U"

of course the easiest would be to pick up a column value as a letter

NT values do not get a number

A_____ _____NT###

B_____1_______C####

C_____2_______RMK###

D____ _______NT####

i am guessing the loop might involve some arithmetic test between the count and the character set 65-90. or maybe a mod thing.

View 11 Replies
View Related
Feb 14, 2013

I have a couple formulas in action to give me the total number of hours worked from start to finish.

Ex:

A1 - 1:30 PM

B1 - 11:45 PM

C1 - I combined the two to get the total number hours worked but set as time: =IF(B1

View 3 Replies
View Related
Jan 27, 2012

I have a spreadsheet wtih a number of rows that contain answers to different criertia in each column.

The row may therefore have cells that either have numbers or letters.

Column A will be the title of the row.

I want to write a function to find the average value of the numbers in any row with title 'x'.

So it would look down column A, and look for those called 'x', and then average all the numbers across all those rows.

I have tried to use =averageif, but I think the fact there are letters in the cells being assessed (which I just want to be ignored) creates an error.

View 9 Replies
View Related
Apr 9, 2013

Some of my numbers turn into dates when I try to import them. Is there anyway around this problem?

I have 500k+ rows with data, so looking through each and every cell is not an option.

View 2 Replies
View Related
Nov 18, 2013

Is there a way for me to enter numbers in my cells and have them a specific color? I want all my odd numbers to be bold red with a yellow highlighted cell, my even Numbers to be green with a gray color cell.

I want it to look like this 7 and 8. I can not seem to find anything to show the high light but basically the red 7 will be in a yellow box and the green in a light gray. I will be entering rows and rows of anywhere from 4 to 8 digits

View 3 Replies
View Related
Feb 26, 2014

Tallying entires from a PDF into excel. Such that

c=1000

d=15000

e=50000

f=100000

g=250000

h=500000

i=1000000

j=5000000

k=2500000

l=50000000

In a cell I would like to type =c+d+c for example and have it say 17,000 rather then me having to type in =1000+15000+1000.

View 4 Replies
View Related
Jan 16, 2014

I created this data worksheet with many tabs and multiple users. On the main tab, something weird has happened and I have no clue how it happened or how to fix it....

View 5 Replies
View Related
Apr 17, 2009

This formula will add up the letters D,N,H with one value

Like =SUM(COUNTIF(B4:AZ4,{"D","N","H"}))*12 will =36

How can I add bN =17 bmN =19 bD=24

=SUM(COUNTIF(B4:AZ4,{"bN","bmN","bD"})) to add up to 60

View 9 Replies
View Related
Jul 8, 2014

I'm working on a function that will sum a row of cells containing letters and numbers. I am currently using :

=SUMPRODUCT((0&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5:S5,"T",""),"TP",""),"TPE",""),"P",""))+0) as my base.

Right now the formula will work with T, TP, and P; however as soon as I type TPE I get #value.

Either way you get the point of what I am trying to do, however my cells can contain any one of the following combinations:

T,TP,TPE

P,PE,

E

View 9 Replies
View Related
Aug 21, 2014

In column A I have the following numbers

13710

14782H

9827

14782

14206B

a formula that looks at this range of cells and returns only the cells that have a letter at the end. The letter range goes A to M

View 6 Replies
View Related
Jan 23, 2014

I am a school teacher trying to adjust my tracking sheet to calculate pupils levels. I am looking for 2 potential formulas that will do the following.

1 - In cell AE I would like to generate a formula that will take the data entered in cells J:5, L:5, N:5, P:5, R:5, T:5, V:5, X:5, Z:5, AB:5 and AD:5 and give an average level.

2 - In cell AH is it possible to generate a formula that will calculate how many levels of progress the pupils are making - In other words I need Cell I to be calculated against cell J to see how much progress the pupils are making - for example if in cell I:5, a pupil is was given a 3a, and then in cell J:5 is given a 4b, they will have made 2 sub levels of progress. As well as this, can that progress then be averaged out across cells I:5, K:5, L:5, M:5, O:5, Q:5, S:5, U:5, W:5, Y:5, AA:5 and AC:5 to give an overall number of of levels of progress? An then..... can I colour co-ordinate the cell so that if the pupils are making 3 or more sub levels of progress it turns green, 2 sub levels orange and 1 sub level red?

Levels work like this

3c

3b

3a

4c

4b

4a

5c

5b

5a and so on

View 3 Replies
View Related