If Cell Contains Text, Return Numerical Value
I need a formula that looks at a Cell and if it contains any text at all it will return a numerical value into a seperate cell. I have done this before but for some annoying reason i cant remember how i did it.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Text In A Cell To A Numerical Value
Basically what I am trying to do is build a formula but I have hit a wall it seems like. what I want to do to complete the formula is, I have 2 names in mind that I want to be able to enter one cell and have excel translate them into a number. So for example if I enter the name John in that cell excel will know that John means the number 3 and then tomorrow I erase John from that cell and enter Jim and excel will know that Jim means the number 2. But again I want to be able to enter a certain body of txt into one cell and have excel recognize it as a number. How can this be done? I have Excel 2003 and 2007
View Replies!
View Related
Formula Return The Address Of The Last Numerical Entry
It's a spreadsheet of about 300 rows, each row corresponding to a certain department. each row is filled with various data, both text and numerical, including various different scores and averages. the columns include daily scores and weekly averages. I want to be able to create a new column that will calculate the time (in days or weeks) since the last score/average was entered. 1. a formula that will return the address of the last numerical entry in a given row. (I've got a MATCH formula that will do this, although it doesn't let me specify different cells or ranges, only the entire row) 2. whichever cell is returned will give the date instead of the value. (for example, if the last value entered is in $F$63, and Column F is headed 23/11/09, then the formula will give the date) 3. then finally, calculate the time between that date and the present time I've tried various different things, both in normal Excel and the VBA editor, and I'm beginning to think maybe this isn't even possible.
View Replies!
View Related
Return Numerical Label For LAST Value Subtracted To Reach Sum Target Value
I am looking for a Formula that can process the following: The Sum Target Value is variable e.g.; 147 I have two columns of numerical values: Column “A” and Column “B.” Column “A” Houses the Numerical Labels that I wish to have Returned when the Sum Target Value is processed /reached  Subtract Sum Target Value as noted below. To Subtract Sum Target Value: Start from LAST nonzero numerical value in Column “B” and Subtract one Cell value at a time (or Sum up the Column) until the Sum Target Value or nearest possible Sum BELOW, the Sum Target Value is reached. In this instance, it is 147. I wish to Sum the values in Column “B” to 147 per the above. The summed values can be below BUT NOT over the Sum Target Value. Return the Numerical Value that is Offset ONE Cell to the LEFT (Column “A”) and ONE Row Above LAST value Subtracted (in Column “B”) to reach Sum Target Value. The Result – Numerical Label should come from Column “A.” Col “A” Col “B” 2003 2051 2105 21511 22010 22515 23016 23510 24011 24529 25020 Reaching the Sum Target Value of 147 in Column “B” would go up to value 26, Label 295 in Column “A”, totalling 131 which is below the Sum Target 147 but including the value of 31 above it, would exceed the Sum Target Value of 147. The required Result is returned from Column “A” Label 290 which is ONE Cell to the LEFT (Column “A”) and ONE Row Above LAST value Subtracted (in Column “B”).
View Replies!
View Related
Return Range Of Numerical Values In Single Column Based On Frequency Percentage
I have a single column of numerical values that may repeat many times within the column. I require a flexible Formula: Use an Input Cell for the specified and changeable Percentage(s) %. Column may be filtered – so only take into account Visible Filtered Cells. The Formula will calculate and Return the numerical range of values that fall between the specified percentage % (using the Input Cell) e.g.; 70%. The Formula should Return two numeric values: a Start Value and an End Value – NOT necessarily the minimum and maximum per se BUT the MIN and MAX of the values that appear 70% of the time in the column; therefore, taking into account Repeat / Duplicate values. The calculated Results: the two numeric values will be returned to separate cells on a new Sheet.
View Replies!
View Related
Numerical Logic Colliding With Text
I have a monster of a sheet going and am working for research purposes. I dislike the interface of SPSS so I use excel to boil down my variables to a smaller state, to which it then goes back to SPSS for the hardcore analysis. Unfortunately, this study has values of "N/A", "Missing", and "." (not entered), so it makes it a bit tricky for me. Say I want to check 10 different scales to see if any are true: =IF(OR(FJ6>=1,ZD6>=1,....etc),"Yes","No")) Problem is the blasted program is reading ".", "Missing", and "N/A" as greater than one!!!!! I've found a way around by making monster formulas that first check that the field is not equal to those three text values, but there must be a faster way! To see what I mean, put a period in one cell (like A1) and write =IF(A1>=1,"Yes","No") and you'll see it returns a Yes.
View Replies!
View Related
Sort NumericalText In Order
I am trying to sort different SKU #'s for my amazon store on excel. I go to Data... Sort... Ascending and it comes out like this... UP1 UP10 UP100 UP101 UP102 UP103 UP104 and so on... i want it to be like this UP1 UP2 UP3 UP4 UP5
View Replies!
View Related
Find Numerical Difference Between Text Values
Hi all, first post here. I've been asked to do something in excel i can't seem to get my head around. My client provides me with data in the form 1c, 1b, 1a, 2c, 2b, 2a, 3c etc... where 1a is the lowest. I need to find a way of working out the difference between these, e.g. edit: 2a  1c = 5 I've been working on it and have made some headway by splitting these with MID function and then using CODE to try and work out a unique ID for each, but i can't help feeling there's an easier way!
View Replies!
View Related
Assign Numerical Values To Text To Allow Calculations
I am working up a workbook template that will be used to process survey results. The way I ultimatley want it to work is that the user can paste data from another application into a worksheet in Excel, and then view analysed results on a second sheet (which are processed via formulae on a third, hidden sheet). The problem I have is this: the data from the program that's capturing them are text values. For example, column B contains answers to a question where participants rate an experience, and the values are either "Excellent", "Good", "Fair", "Poor", "Awful". I want these to be converted to numerical values, on a scale where "Excellent" = 5 and "Awful" = 1, so that I can then average these. So, the question is: Can I somehow tell Excel that "Excellent" = 5 and "Good" = 4 etc, and then use AVERAGE(B:B) on the text data and get a number back? I did think of having an intermediate sheet that used VLOOKUP to create a copy of the first sheet (where the user pastes the text data) with the text replaced by numbers. But, because I don't know in advance how many rows will contain data (i.e. how many survey results there will be), I have to assume on the high side and copy down 50,000 rows. But, this takes ages to calculate.
View Replies!
View Related
Displaying Months From Cells As Their Text (not Numerical)
The formula contained within these cells equals =MONTH(C84) [the date 2 cells above it]... to which I've formatted the cell to "mmmm" and it displays "January" across the board. Can anyone explain why this is occuring and how I can fix it please? I'm aiming to graph this information and can't accurately do it using the moving monthly system i've put in place if I can't change the months dynamically like this. Secondly, and on a side note. Line 84 currently takes todays date (not currently todays date in the example screenshot) (=TODAY()), and to get to the previous 13 months I deduct 31 days. I can foresee this as being a problem further down the track especially when leap years come into play. Is there a function in Excel where I can simply deduct 1 month from another? =MONTH(C84)1 ?
View Replies!
View Related
Comparing Grades (text) To Give A Numerical Score
I am creating for the pupils at my school. Comparing the grades they were predicted and have achieved I would like to give them a score. I have already achieved this but it means wasting 3 columns for each subject to work it out. The ambition is to do all the working out and assign a point score value in 1 column. The spreadsheet it attached if anyone would be kind enough to have a look and see if there may be a formula which will do what I need.
View Replies!
View Related
Find Cell Value In Column & Return Text In Adjacent Cell
I have a drop down list in a merged cell B12F12 and B13F13 and B14F14 . . . B30F30. I need the adjacent merged cell to populate a reason (text) based on the text answer in the drop down list or the entered text in the first merged cell. For example in the cell B12F12 the user picks from the list or types in "Amiodarone." I want the adjacent merged cell G12J12to automatically fill with "Heart Rhythm." I also want to be able to set up multiple if  them statements like if Amiodarone is entered then fill adjacent cell with Heart Rhythm and if Toprol XL then fill adjacent cell with Heart / Blood pressure and if simvastatin then fill adjacent cell with Cholesterol, etc. I have about 30 different options for cell 1 that I want to have auto fill in cell 2 based on the contents of cell 1. I've attached my file. I want the user to be able to choose from the list or type the drug name in.
View Replies!
View Related
If A Cell Has Certain Text Then Return Value?
How do i write an If function where if a cell has a certain text in it, it would return a value to onather cell. For instance: If cell B1 has "red" in it the cell B4 must be "6" If cell B1 has 'blue" in it, then cell B4 must be "7" and so on with certain text returning a certain value?
View Replies!
View Related
Return A Value If Cell Contains Rather Than = A Text String
I want a formula or function that will return a count value if a cell contains a string rather than returning a value if the cell equals a value. For example, =COUNTIF(A$1:A$19703,F1) returns a value to cells in column G if the value in column F equals any exactly matching value in A. I need a formula that works like the filter option "contains" so that if any cells in col A merely contain a string rather than exactly match or equal a string from column F I can get a count value returned.
View Replies!
View Related
Return Text Depending On Another Cell
I have a very long excel sheet. What I want to do is, depending on the input in one column (text), return a another text in another column, for the whole sheet. I tried to with Select Case Statement and ifthen, but couldn't figure it out since I don't have much VBA experience.
View Replies!
View Related
Return Result Based On Text In Cell
I have a spreadsheet where in cell E2 there is a drop down box with the following options to select: Warranty Replacement, Insurance Claim, Billing Issues, Retention Opportunities. In cell F2 is where wait time minutes are generated depending on what is selected from the drop down box in E2. I am trying to create a nested IF formula for the following scenario: Warranty Replacement = 20 Insurance Claim = 20 Billing Issues = 15 Retention Opportunities = 20
View Replies!
View Related
Choose & Return Cell Based On Text In Another
I am attempting to choose a cell containing a text string based on information placed in a raw data area. My output from this formula will be a line of text picked from yet another reference cell, based on each of the four choices. Removed non pertinent information and edited for clarity.
View Replies!
View Related
Return Formula Value/Text Based On Many Cell Conditions
This is to manage which departments (approxiamately 30) within a business need which compulsary training (approximately 11 courses) Spreadsheet currently reads list of new employees and I want to be able to have "YES" or "No" values under the different courses Is there a formula/function that i can use (like the IF Formula) to complete the following information; EG: =IF(OR(A3=H2, A3=H5 etc... ), "YES", "NO" Column H lists all departments Column A lists deaprtments A3 representing the 1st Department needing training
View Replies!
View Related
Compare Cell To Many Cells & Return Relevant Text
I have a if function that i am creating in excel 2000, and it has 8 variables. I am trying to put it in a macro as it is not working as a nested if function. Here is the current if function... =IF(B2=C2,"OK",IF(B2=D2,"OK",IF(B2=E2,"OK",IF(B2=F2,"OK",IF(C2=0,"OK",IF(D2=0,"OK",IF(E2=0, "OK", IF(F2=0, "OK", "INCORRECT")))))) the excel file is attached for further clarity
View Replies!
View Related
Multiple Numerical Formats In A Single Cell?
In a single cell, I would like to show 2 values: a currency and a percentage. So A1 and A2 has values, say 50000 and .10 respectively (these are derived via formula, if it's of any relevance). What I ultimately want is "$50,000 and 10%". I've originally thought about adding the "$", " and ", and "%" separately, but I need the currency value to include commas.
View Replies!
View Related
Count Or Sum Of Nonnumerical Cell Content
I'm working with a fairly large spreadsheet, and need to find out how many times a specific word or numerical value comes up in a column (for instance, I might need to know how many times 'Anderson' comes up in column B). Every time I try to use the sum, dsum, sumif, count, dcount, or countif functions, however, I keep coming up with either 0 or the #VALUE message. This is very frustrating, as you can well imagine. I could try to sort the data by the various terms and values I need and then physically count everything but this strikes me as timeconsuming, prone to error (we're talking a few thousand entries, here), and something Excel should theoretically be able to do anyway if only I knew how to ask it.
View Replies!
View Related
Inserting A Numerical 1 Or 0 Into A Cell Depending On The Values Of Two Other Cells
I'd like help writing a single formula that assigns either a numerical 1 or 0 to a cell based on values from two other cells. By way of background, I'm working with three columns. Cells in column F contains values from 1 to 4. Cells in column G contain values from 5 to 11. Cells in column H are currently blank. I need a single formula that will insert either a numerical 1 or a 0 into column H according to the following conditions.If F3 = 1 and G3 = 5 or 6 or 7 then let H3=1 otherwise let H3=0 If F3 = 2 and G3 = 7 or 8 or 9 then let H3=1 otherwise let H3=0 If F3 = 3 and G3 = 9 or 10 or 11 then let H3=1 otherwise let H3=0 If F3 = 4 and G3 = 9 or 10 or 11 then let H3=1 otherwise let H3=0
View Replies!
View Related
Return Text From A Mixed Text/value 3d Array Result
I've been searching the forums and web trying to figure out how to extract a text value from a 3D array result, to no avail or well beyond my understanding. I've attached a file which shows an abbreviated version of the search (from the original 49 col x 400 row, which shows the name to date array which can return a sum. What I'm looking to do is identify within this array result if there has been a text entry, and return the text as opposed to a sum of the remaining values. This is essentially a double check to make certain that there aren't hours scheduled when other events such as vac/loa/etc are also booked.
View Replies!
View Related
Partial Text Lookup In Text String And Return
Cell H1 has a variable string of references for eg "FI570783AQ3516346EQ3516346FXVB123456" I want to return the reference beginiing with "FXVA" so it would ignore the rest and only return FXVB123456  this should always be 10 characters. Just to add another complication to the mix, there may be 2 "FXVA" references in the string and i want to get both (these can be in the same string so FXVB123456FXEL123456  but this might not happen regularly.
View Replies!
View Related
Return Text Based On Text Existing In Range
I am trying to Lookup a country to see if it is classified as a "Developed Country". My formula would be in cell AA4. I want to see if the country in cell B4 is on a list of developed country's on another worksheet. IF the country is found on that list cell AA4 displays Developed. If the country is not on the list, cell AA4 displays Emerging. I have tried an IF statement using the Match function and it does not work. =IF(MATCH(B4,'Developed Country List'!$A$2:$A$37,0), "Developed", "Emerging")
View Replies!
View Related
SUMPRODUCT To Return Text
The below table is a small extract from my data. I want the formula in C3 to return the contents of A4, that is, where Amount = D3, Policy = E3, Status A3. The following formula returns 0, because I'm trying to return a text string rather than a numerical value:....
View Replies!
View Related
SUMPRODUCT Use To Return A Text
If recently learned how to use the sumproduct function as a lookup for multiple criteria and it was working great, but I realize now all the values that I was returning were numbers. Is there a way to use the sumproduct under the same circumstances but to return a text value? COL ACOL BCOL C A1X A2F A3G B1E B2S I would want the lookup with conditions of ColA=A and ColB=2, so the return should be "F" =SUMPRODCUT(($A$1:$A$5="A")*($B$1:$B$5=2)*$B$1:$B$5).
View Replies!
View Related
If Statement That Return A String Of Text?
not sure if an "IF" staement is appropriate? I have 3 columns containing data: column A has text in it, example: A2="red", A3="green", A4="blue" column B has 3 letter month in it and they could have repeats example: B2="Jan", B3="Feb", B4="Jan" C2="Jan" in D2 list anything column A that matches C2 example: D2="red, blue"
View Replies!
View Related
Vlookup With Multiple Text Value Return
I'm trying to create a formula that returns with all the text values for all the conditions. So similar to a sumproduct where the formula returns a summed value of ALL the conditions i need the same but for text. hello no hello no hello yes goodbye no formula would return with (no, no, yes).
View Replies!
View Related
Array Formula To Return Text
I have an Array formula that returns a value from several rows below: =MAX(IF($P$8:$AQ$8="Today",$P13:$AQ13)) Row 8 contains only text and only one occurrence of the word "Today". Row 13 has only numbers. The corresponding value directly below the column containing the word "Today" is returned successfully. I now need a similar formula to return a text entry located in the same column but on the previous row (row 12) directly above the value just returned and this row contains only text entries. Using the above formula and referencing $P12:$AQ12 returns 0 as expected.
View Replies!
View Related
Look For Two Values And Return A Text String
Say I have a table like this: Value 1Value 2Name 11Potential client 13Loyal client 25Happy client Now I have two separate columns with values for "Value 1" and Value 2". How can I tell Excel to look in the table above and return the name?
View Replies!
View Related
Return A Text Value Based On Criteria
I'm trying to create a formula that will allow me to pull test from a list (auto populate if possible). Essentially you will see on the second tab, a list of projects "Cali" for example. I'm trying to find a formula that will allow me to show the Customer and Life Cycle on the first Tab. If possible the Project Name too. Essentially I want to be able to have all the data inputed into Tab 2 and let Tab 1 condense it into the designated fields. So basically what will allow me to see all of the "Cali" projects, and from that generate the Customer and Life Cycle (and Project if possible) on Tab 1. Keep in mind this does need to be automatic updating, so that as we input more information on Tab 2, it will automatically kick into Tab 1.
View Replies!
View Related
Return Text Based On Criteria
Example: I have 2 sheets, a pivot and a data sheet. When selecting a different option on the pivot i want information returned from the data sheet (which is explanations of the information contained in the pivot) I need to add 2 criteria points.
View Replies!
View Related
Return Lowest Based On Text
I am looking for a formula that will return the lowest value in a range, expressed as a letter. The relative values of these letters are: F=0 P=1 M=2 D=3 For example, If in cells A1:A4 we entered the values F, P, M & D in A5 the result would be F (as it is the lowest value in the A1:A4 range). I have included an example worksheet with fictional data and the intended result in the Final Score column.
View Replies!
View Related
