Tracker
HOME    TRACKER    Excel

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

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

Giving A Text Cell The Value Of A Numerical Cell
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.

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.

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

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.

Assigning Text Numerical Values
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)

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.

Sort Numerical-Text 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

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!

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.

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 ?

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.

Find Cell Value In Column & Return Text In Adjacent Cell
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.

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?

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.

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

Return Value Depending On Text In Cell
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

Return Text In Another Cell Based On That Data
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.

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

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.

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

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.

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

Display A Msgbox For Numerical Value Of Each Cell
-In cells J6:P11
- Display a MsgBox for the value in J6; Then
- Display MsgBox for the value in K6; Then
- Display MsgBox for the value in L6
- etc, etc

Status Formula (find Out The Formula Where Numerical Valued Between 1-2000 Equals True And Numerical Under 0 Equals False)
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.

Search Formula (return A Statement If A Certain Month Is Contained In The Text Within Another Cell)
I am trying to write a formula that will return a statement if a certain month is contained in the text within another cell. Formula is =IF(ISERROR(SEARCH("Dec",Assumptions!B2)),"Ensure Journal is Non Reversing","")

Cell B2 contains a date in the format of Dec 08, so if this date contains Dec, then return "Ensure Journal is Non Reversing", if it doesn't then leave the cell blank.

At the moment it is putting in the first test for every month I select and not changing to blank.

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.

Count Or Sum Of Non-numerical 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 time-consuming, 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.

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

Reference Sheet (return The Value Of Cell B2 On Whichever Sheet The Text Of A1 Says)
If I have a cell that has as its contents as sheet name, is there anyway I can use the cell's address to reference that sheet?

As an example, say I have in Sheet1, cell A1, the text Sheet2. And let's say I want to return the value of cell B2 on whichever sheet the text of A1 says. So, on Sheet1, I might have this:

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.

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.

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

Formula: If Value Return Is = To Value Of Cell Above Then Find Return Next Value
Is there a way with the following formula to tell it that if value return is = to value of cell above then find return next value?

Multiple A Logical Result Array With A Text Array And Return Text
I need to multiply an array of logical results ( returned as {1,0,0,0,1,0 et.}) with a text array (a reference column) and return the text in the reference column in case the value in the logical array is 1.

Return Value Within Text String
Can you please assist in helping with a formula that will return the value of the characters (no matter the length) between the "."?

123456.B.78.C.321
12345.B.78.C.321

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

Is There A Function To Return >3 (text) As 3
I have a cell with a text string >3, and would like it to return the number (not text) 3. I could use Right but since the number will not only be single digit I don't think this will work.

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

Return Text Associated With Maximum Value
If I have names in one column and amounts in the next column, how do I determine the maximum of the amounts and return the associated name from the other column?

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"

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

Return Position Of Text Within Range
If cells a4:aa4 contained various results in the format "TeamX won by xx runs", how could I search the row for a particular team, and return the column reference it is first found in?

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.

Look For Two Values And Return A Text String
Say I have a table like this:

Value 1---Value 2---Name
1---------1---------Potential client
1---------3---------Loyal client
2---------5---------Happy 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?

Search For Text And Return Value In Another Column
Imagine the columns below are labelled A - D. Is there a way to search for the value 'SPLT' and return the value in column D?

Great Scot Dried Peas 3610.6366
GT SCT GRN SPLT PEAS480.151
GT SCT MRWFT PEAS1230.2158
GT SCT SPLT PEAS 1900.3157
Haven 370.10

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.

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.

Concatenate Text With Soft Return
I would like to concatenate text comments from 14 worksheets and place a soft return between each next set of text. To further complicate, not all of the worksheets will have comments.

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.