If Cell Contains Text, Return Numerical Value

Jun 16, 2007

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.

ADVERTISEMENT

Find Text In Column - Return Numerical Value

Dec 15, 2011

I need to create a formula that searches column E for text, let's say "BANANA", and when it finds that text, the formula returns the numerical value of the cell in that same row in column B. Let me know how this can be done.

View 2 Replies View Related

Text In A Cell To A Numerical Value

Jan 20, 2009

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 10 Replies View Related

Giving A Text Cell The Value Of A Numerical Cell

Mar 20, 2008

I need to know if you can give a cell with text (such as a name) the value in a different cell (eg a total value cell).

Basically when the name is displayed I can add that cell to a team and therefore easily calculate a total teams worth by dragging names into the team's column.

View 9 Replies View Related

Formula Return The Address Of The Last Numerical Entry

Feb 25, 2010

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 9 Replies View Related

Getting Function That Will Return Numerical Value Based On Dropdown List

Aug 3, 2014

What I am trying to do is have a drop down list in one cell and have the next cell bring in the correct price for the item in the first cell. Sounds fairly easy? Well both lists are 200 items.

I have been trying to do this with just three items so far. On sheet two, I have created two lists. The first list in column A contains dogs, cats, mice. The second list in column B contains 1, 2, 3. On sheet one, I have created the drop down list for the animals in the A12 cell. I would like for B12 to auto populate the corresponding number to the animal without having to create an if formula for each animal and number.

View 4 Replies View Related

Return Numerical Label For LAST Value Subtracted To Reach Sum Target Value

Oct 28, 2005

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 non-zero 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 14 Replies View Related

Return Range Of Numerical Values In Single Column Based On Frequency Percentage

Oct 25, 2005

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 9 Replies View Related

Excel 2013 :: Check Cell For Text And Return Corresponding Text In Adjacent Cell?

Jan 28, 2014

I have 2 tabs in a 2013 workbook. Inventory Receipts and lookups. One of the Data verification lookups I have is a drop down list in each cell in Column B (eg: Cat, Dog, Mouse) In the lookups tab I have another cell range containing the sounds (eg: Meow, Bark, Squeak).

What I am trying to acheive is, if B2 contains Cat, then return Meow in B3, if B2 contains Dog then return bark in b3 and if B2 contains Mouse return squeak in B3. Ideally I would like the formula to return the text from my lookup sheet (eg: 'Lookups'!C2,'Lookups'!C3,'Lookups'!C4). So depending on which option they choose from my animal drop down list .. the correct noise would automatically fill in.

View 9 Replies View Related

Returning A Numerical Value From Text

Oct 25, 2013

I'm trying to streamline my spreadsheet a bit. Sometime last year I managed to use the formula found (on the attached file) in cell K4 to assign a numerical value to a lettered grade. However, it's been a year or so since I thought about it and so I've completely forgotten how to edit it to make it more appropriate for this spreadsheet!

Essentially what I'm trying to do is a workaround for what I've already done in columns E and F: I'm trying to show the difference between two grades (letters) as a numerical value. Is there a way to do this, or is the VLOOKUP work around I've used in columns E and F going to be the simplest way?

View 1 Replies View Related

Compare Text Strings In Two Columns And Return Text From Adjacent Cell

Feb 28, 2013

how to Chk the text string in particular cell, compare it with a super set column and get the full from of the text string from another corresponsing column and the output will be corresponsing full form of the chked text string?

View 6 Replies View Related

Numerical Logic Colliding With Text

Oct 13, 2008

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 4 Replies View Related

Assigning Text A Numerical Value In Excel?

Apr 21, 2013

I am a teacher and need a column of student names (which will all be different) but also need to assign them a numerical value of 1 to analyze my data. How can I do this?

View 3 Replies View Related

Assigning Text Numerical Values

Jul 9, 2006

Is it possible once you have assigned text a numerical value (example: Adam = 12) to add them together? (example: adam =12 and bob = 8, therefore adam + bob = 20)

View 9 Replies View Related

Sort Numerical-Text In Order

May 14, 2008

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 3 Replies View Related

If Cell Contains Certain Text And Certain Text Then Return Predefined Text?

Apr 10, 2013

I'm trying to get something like - If a cell contains certain text AND certain text then display set text OR If a cell contain certain text AND certain text then display set text.

For example:
Vauxhall Corsa 1.5Litre Petrol = vauxhall corsa petrol
Vauxhall Corsa 1.5Litre Deisel = vauxhall corsa Deisel
Vauxhall Nova 1 litre Petrol = vauxhall nova petrol

So it the formula is effectively searching for the car type and the fuel type and then returning the relevant set text. So far I'ver only been able to find IF functions criteria to identify one feature of the text.

I'm thinking it's got to be variation on =IF(ISNUMBER(FIND("Vauxhall Corsa",A1)),"Vauxhall Corsa Petrol") but there should be something else in there like =IF(ISNUMBER(FIND("Vauxhall Corsa"&"Petrol",A1)),"Vauxhall Corsa Petrol")

I know this is wrong but just trying illustrate my thinking. I need it to pick up vauxhall corsa AND petrol. I understand that I would have to stick a lot of IF functions together in a string, but that is fine as long as I can ask it to pick certain text AND certain text within a cell.

View 6 Replies View Related

Displaying Months From Cells As Their Text (not Numerical)

Oct 14, 2009

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 2 Replies View Related

Find Numerical Difference Between Text Values

Nov 21, 2008

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 9 Replies View Related

Assign Numerical Values To Text To Allow Calculations

Jan 31, 2010

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 9 Replies View Related

Comparing Grades (text) To Give A Numerical Score

Nov 3, 2009

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 12 Replies View Related

If Cell Contains Specific Text Return That Text

Jun 12, 2014

How would I pull only a specific word from a text cell? If you see my example, in one column I have various job titles. If A2 contains "Manager", then in B2 return "Manager. If A1 contains "Coordinator", return "Coordinator. Etc.

Excel Forum.jpg

View 3 Replies View Related

Excel 2002 :: Modify Formula To Merge Numerical With Text

Apr 21, 2012

I am using the following formula to find out the No. of "HH" in the range given and I am getting the results simply with numerical value as 2, 3 etc. I want the result should be displayed as 2HH, 3HH depending upon the No. of "HH" available in the given range. The result should be displayed as (the No. added with the text "HH").

=IF(AND(DJ10="HH"),COUNTIF(DF10:DJ10,"HH"),"-")

I use excel 2002.

View 2 Replies View Related

Data Validation Formula For Set Of Numerical And Text Based Values

Apr 30, 2014

I am quite inexperienced with Excel and I am struggling to understand how should I set up a formula, within the data validation function to achieve the following:

I need to restrict a certain set of cells, for people to be able to input only the following values: "1", "0.5", "A", "H", "0.5A" (or 0.5a), "0.5H" (or 0.5h)

And I would need the data validation tool not to include blank cells.

View 1 Replies View Related

Find Cell Value In Column & Return Text In Adjacent Cell

Jun 10, 2008

I have a drop down list in a merged cell B12-F12 and B13-F13 and B14-F14 . . . B30-F30.

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 B12-F12 the user picks from the list or types in "Amiodarone." I want the adjacent merged cell G12-J12to 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 8 Replies View Related

If A Cell Has Certain Text Then Return Value?

Aug 6, 2009

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 2 Replies View Related

Search For Text Within Cell Then Return Contents Of That Cell?

Jun 19, 2014

So right now I have a spreadsheet that looks something like this:

A
B
C
D

[Code]....

As you can see, the stock names are slightly different in columns A & C (CORP. vs CORP, CO vs CO., etc). I need a formula in column D that searches column A for the first word in column C, and then retrieves the contents of that cell. I want to do this because I will then use a vlookup in column E to get the ticker for the stock.

Right now I have: =IF(ISERROR(SEARCH(LEFT(C1,FIND(" ",C1)),A:A,1)),A:A,"")

This formula searches column A for EXXON, but does not return the contents of the cell. Instead, it returns the contents of a different cell in the column.

View 4 Replies View Related

Return Yes If Text Is Found Within A Cell

Dec 24, 2013

I currently have the below formula to search for text of interest (A1) within each cell of a column (B6:B30).

=IF(ISERROR(MATCH(A1,$B$6:$B$30,0)),"NO","YES")

The formula works perfectly if there is only a single value in the cells. But not so well when the cell has multiple values separated by a comma.

For example:

Column B

X
Y
Z
T, X, Y, Z

It would return a "Yes" for X, Y, and Z because those are found individually. However, T will appear as a "No", because it is found within a cell that is separated by commas.

View 4 Replies View Related

Return A Value If Cell Contains Rather Than = A Text String

Nov 16, 2004

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 9 Replies View Related

Return Text Depending On Another Cell

Jan 8, 2007

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 if-then, but couldn't figure it out since I don't have much VBA experience.

View 6 Replies View Related

Return Value Depending On Text In Cell

Mar 1, 2008

I have a cell A5 in a worksheet that can be either E or W. I am looking for a formula in Cell A10 in the same worksheet to return numeric values of either +1 if cell A5 is W or -1 if cell A5 is E

View 3 Replies View Related

How To Return Cell Contents If It Contains Partial Text

Jan 2, 2014

Below is a small sample of Column A from a spreadsheet I'm working with. All cells in the column begin with a 6 digit number:

115383_BOSTON
115384_NEW_YORK
115385_PORTLAND
115402_LOS_ANGELES
115403_WACO
115010_SAINT_PAUL
115011_SAN_DIEGO
115130_WEST_NYACK
115021_CHAPEL_HILL

What formula would allow me to enter only a 6 digit number and return the entire cell contents, i.e, search for 115403 and get a return of 115403_WACO ? I have tried INDEX and MATCH, but I'm not getting the results I need.

View 7 Replies View Related

Return Text In Another Cell Based On That Data

Apr 30, 2009

I have a cell with a %. I want to return text in another cell based on that data i.e. between 90 - 100 = excellent, 75 - 90 = good, or something like that.

View 3 Replies View Related

Macro - Add Carriage Return To End Of Text In Cell

Oct 31, 2007

I am looking for some assistance in creating a macro that would allow me with one key stroke to enter a cell in edit mode, add a carriage return to the end of text in the cell, then move the cursor to the next cell.

View 9 Replies View Related

Return Result Based On Text In Cell

Oct 26, 2007

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 5 Replies View Related

Choose & Return Cell Based On Text In Another

May 2, 2008

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 3 Replies View Related

Excel 2010 :: Delete Cell Contents If Cell Contains Numerical Value?

Oct 8, 2013

Recently I have been practising writing my own macros in VBA. I am using Excel 2010.

I have been trying to write a macro to look at two columns in a worksheet (say, columns D and E) and to delete the contents (not the formatting, and I don't want to delete the cell itself) of that cell only if it contains a numerical value (e.g. 1, 2, 256 or any other number). If it finds a cell with letters, then it wouldn't delete the contents of the cell. The cells are currently formatted as "currency" cells.

I have been trying all sorts of things and I just can't get it to work. I can write a macro to clear a range easy enough, but I am stuck on getting it to delete the right stuff. I won't embarrass myself by uploading the code that I have been using..

View 7 Replies View Related

Compare Text In Three Cells - Return With Yes Or No In Fourth Cell

Jul 10, 2014

I have three cells - A1, B1, C1 and each contains a value of yes or no.

If more than two of the cells say "yes", I want the fourth cell (D1) to display "yes."

If less than two columns say "yes", I want the fourth cell (D1) to display "no" .

View 3 Replies View Related

Formula To Find Text And Return Its Cell Address

Sep 6, 2013

Is there any formula to find text and return its cell address? This is like when we use Ctrl+F function to find a text, and Excel then highlight the cell contains the referred text. At the same time, the name-bar displays the address of the cell. Now, is there any formula that can perform such task like this?

View 9 Replies View Related

Return Formula Value/Text Based On Many Cell Conditions

Oct 16, 2006

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 8 Replies View Related

Compare Cell To Many Cells & Return Relevant Text

Feb 22, 2008

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 3 Replies View Related

Status Formula (find Out The Formula Where Numerical Valued Between 1-2000 Equals True And Numerical Under 0 Equals False)

Dec 12, 2009

I'd like to find out the formula where numerical valued between 1-2000 equals True and numerical under 0 equals False. Examples are in the attachment.

View 3 Replies View Related

ADVERTISEMENT