Difficulty With IF Function For Cells Containing Both Numbers And Letters?

Feb 1, 2013

I have a sheet with part numbers, costs and wish to add varying number to the cost in each row, based on a certain aspect of the part number:

A
B
C

[Code].....

In the example, 01/1X/2X/3X are the differentiating aspects of the part number. To rows in column A containing 01 (preceding the dash) I want to add nothing, 1X+.75,2X+1.5,3X+9.

I have researched the nested IF formula and can use it in a test as long as column A contains either numbers or letters. The problem I have is because there is both numbers and letters, the logical argument won't validate "if true". Here is my formula (omitting my attempts with parenthesis etc. around the letter in the logical argument):

=IF(IF(A1=1X,B1+.75,IF(A1=2X,B1+1.5,IF(A1=3X,B1+9,B1)))

View 9 Replies

Difficulty With IMPOWER() Worksheet Function

Jun 7, 2008

1) The cubic root of complex numbers "x+yi" are calculated using IMPOWER().
The w/s function appears to return incorrect values when the coefficients of the complex number are -ve.

2) For example:
A1:: -8+0i
B1::= IMPOWER(A1,1/3)
the function returns: 1. + 1.7320508i
instead of the correct value of -2.

3) The problem appears to be consistent with calculating theta [=atan(y/x)] as "pi" instead of "0" for the above example "-8+0i"

View 9 Replies View Related

Sum Cells Containing Numbers And Letters

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

Conditional Format Cells Containing Numbers And Letters - Ignore Cells With Number Only

Jul 11, 2014

I have a column of numbers and want to make sure everything has been entered correctly from our scanning software. Basically, I want to automatically highlight any cell that has any letter in it (e.g. z12o2 instead of 21202 or R705 instead of 5705), ignoring any cells that contain only numbers. I haven't had any luck using conditions based on formulas like =ISTEXT.

View 2 Replies View Related

Excel 2010 :: Difficulty Breaking Link Possibly Caused By Name Function

Sep 11, 2013

I have researched a problem I am experiencing in Excel 2010 and have had problems in the past with previous versions. It seems that occasionally I run across a link that just refuses to break. During my research I have found some reports that this may be linked to the "Name" function. !!!Bing!!! the light comes on. When I copied my template to my new folder and tried to cut and paste data into it I got a message saying something like "A formula or sheet you want to move or copy contains the name "...", which already exists on the destination worksheet." and I opted to replace the existing name with the new one.

Apparently I created an external link at that point and one that cannot now be broken unless I delete my named range and start over the naming process. I mention this here because I see lots of people having this problem and the stock answer is to goto the Edit Links menu and attempt to break the link. Try as they and I might, this doesn't work. I also saw a post about it being a phantom validation problem and that too panned out although I can see how the validation could be linked externally and create this problem.

Does this accurately describes the issue and is there a remedy besides re-creating the "Names"?

View 1 Replies View Related

Formatting Cells For Both Numbers And Letters

May 15, 2013

I need to format a column of numbers so that it will sort alphanumerically.

All the numbers have 4 digits, and some of them have an extra letter on the end eg A B C

How can I custom format the column so that I get the numbers in order and the letters in sequence with them?

View 9 Replies View Related

Split Cells Based On Letters Vs. Numbers

Mar 3, 2009

I have a column in one of my spreadsheets that I would like to split. There's currently not anything that can be used as a delimiter, nor can I use fixed width. the cells in question start with numbers and end with letters.

View 7 Replies View Related

Split Cells Based On Letters And Numbers?

May 20, 2013

I have an excel sheet here with the data as

1.0 Develop Vision and Strategy
1.1 Define the business concept and long-term vision
1.1.1 Assess the external environment
1.1.1.1 Analyze and evaluate competition
1.1.1.2 Identify economic trends
1.1.1.3 Identify political and regulatory issues
1.1.1.4 Assess new technology innovations
1.1.1.5 Analyze demographics
.... etc

I need to split this into two columns column A should contain 1.0 and column B should contain Develop Vision and Strategy . Ex: 1.1.1.2 Identify economic trends in column A then column B - 1.1.1.2 column C - Identify economic trends.

View 2 Replies View Related

List All The Combinations Of A Group Of Cells Containing Letters, But Not Numbers

Feb 9, 2008

I have 7 cells containing strings but not numbers on a row.

Now I want to list out all the combinations of drawing out 3 cells out of these 7 cells while the remaining cells that haven't been drawn out could also be listed out one column next to the drawn cells.

For example, I got 7 cells like this.

A B C D E F G (each letter in ONE cell)

And I want to list out all the combinations like this:

ABC DEFG
ABD CEFG
ABE CDFG
ABF CDEG

etc.

For more details, please refer to the attached sample (an .xls file being zipped).

View 14 Replies View Related

Adding Numbers Separated From Letters In Multiple Cells

Jul 7, 2009

I have column of cells containing entries such as V1, V3 and V7. I'm trying (and failing) to come up with a formula to separate the numbers from the letters and add them together: V1, V3 and V7 would together give 11.

I can use the MID worksheet function to separate my numbers from my letters on a row-by-row basis, but I can't work out how to do the whole thing in one fell swoop. =SUM(MID(A1:A10,2,2)) doesn't work, for example.

View 9 Replies View Related

Removing Two Letters From A String Of Letters And Numbers

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

Remove Letters From A Column Containing Both Numbers And Letters

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

VBA Find Function - Search Through Selected Range Of Cells For Key Letters

Jan 6, 2014

I am trying to code a macro that will search through a selected range of cells for key letters, for instance this cell may contain any combination of B, C, Te, Tc, RH, or LH. I would preferably like to search with capitalization being a factor but it is not a deal breaker. Below is a sample of what i have if the cell has a B, C it works for B but ignores the C i need it t o recognize both.

Code:
If InStr(1, ActiveCell.Text, "B") Then Range("O" + CStr(ActiveCell.Row)).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0

[Code] ........

View 9 Replies View Related

How To Sort Numbers With Letter Prefixes And Letters Mixed With Numbers

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

Average Numbers In Table Containing Numbers And Letters?

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

Sum Letters To Numbers?

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

Why Are Numbers Above Letters

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

Letters To Numbers

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

Extracting Numbers With Letters

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

Formula Used With Numbers And Letters

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

How To Convert Numbers To Letters

Apr 5, 2007

i want to convert numbers to letters in the excel Sheet
I.E: when the number is 1000.00$ it write in another cell
on thousand dollar only

1500.00$ one thousand five hundred dollar only

View 13 Replies View Related

Assigning Numbers To Letters

Oct 26, 2009

I have a word, for example ROCK. I asigned numbers to the word. R=1,O=2,C=3,K=4. My Question: If I type in ROC it must return 123. If I type in KR it must return 41, etc. Is there a basic formula I can use to solve this?

View 4 Replies View Related

Allow Only Letters In Cell (no Numbers)?

Oct 7, 2010

How do I do data validation/cell formatting so that only alphabetic characters can be entered and no numbers?

(We use the cell for First Name, Last Name, etc. and I don't want potential entries like Mr. 56565 55454 because the cells are formatted as text and allow alphabet and numbers.)

I tried =NOT(ISNUMBER(H6)) for Data Validation, but that didn't work.

??

VR/Lost

View 14 Replies View Related

Adding Letters With Numbers

Jan 30, 2013

I have in cell A1 the number 2 and in cell A2 the letter m.I want to add these two cells in cell A3 so as to appear 2+m.So i type in cell A3 =A1+A2 but it appears error.Is there a way to do what i am asking?

View 3 Replies View Related

How To Autofill Numbers With Letters

Feb 13, 2013

How to autofill numbers with letters in excel 2007. Eg. 74cm, 80cm so on....?

View 4 Replies View Related

Macro - From Numbers To Letters

Aug 8, 2013

Is there any formula or macro which gives the result from numbers to letters..? say like; 2,471,803.40 is in number and I'm looking for the result as 'RUPEE TWO MILLION FOUR HUNDRED SEVENTY ONE THOUSAND EIGHT HUNDRED THREE AND FORTY PAISE ONLY'. Is it possible to create macro to get this type of result..?

View 9 Replies View Related

Converting Numbers To Letters

Jun 26, 2007

We are doing a database conversion and I have been tasked with converting all our old promotion codes to a new system. The long and the short of it is this:

I am trying to write an equation that will look at two cells (say A1 & B1) which when put together could be one of the following numbers (01,02,03,04,05,06,07,08,09,10,11,12) and translate them to a letter (A,B,C,D,E,F,G,H,J,K,M,N)

So 01=A, 02=B and so on...

I was trying to use if statements, but there are too many.

View 9 Replies View Related

Sort Containing Both Letters And Numbers

Jan 4, 2008

I have a list I want to sort containing both letters and numbers. Right now the regular sort sorts like this
ABC-1
ABC-11
ABC-12
ABC-2
I want it to sort like this and don't have a clue how to do it
ABC-1
ABC-2
ABC-11
ABC-12
I am using excel 2003

View 9 Replies View Related

Force Use Of Letters And Numbers Only

Oct 23, 2009

I have an excel sheet where the user enters a bunch of info. In one of the cells, they enter an "account code" - this is then used to SAVE AS the account code.

The problem... if they enter things into this cell that causes the filename to fail (eg: *, /), the code errors out. I can error handle this in VBA at the time of the save, but I would rather prevent them from entering anything that is not a letter or number in the first place.