Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

[SOLVED] Ignore text, but calculate numbers in cells

Hi,

I need a formula that will total cell A18:B18 but will ignore the text in B18.

All data in "Description" rows are formatted as a drop down list which contain the product or service and the price.

See attached file

Thanks,
Jeff


Post your answer or comment

comments powered by Disqus
I have the following type of data in a single cell. I need to remove the text and leave the numbers (with spaces so I can put the numbers in seperate cells).

DEMOTEXT DATA Boston MA 1 0.6 1 0.5 2 0.6DEMOTEXTDATA DATA Boston MA 8 4.4 6 3.3 14 3.9DEMOTEX DATA DATA Boston MA 1 0.6 0 0.0 1 0.3THISISDEMO TXT DATA DATA Boston MA 1 0.6 0 0.0 1 0.3TXT DEMO DATA DATA Boston MA 1 0.6 4 2.2 5 1.4

I would like the following output:
1 0.6 1 0.5 2 0.6
8 4.4 6 3.3 14 3.9
1 0.6 0 0.0 1 0.3
1 0.6 0 0.0 1 0.3
1 0.6 4 2.2 5 1.4

Thank you in advance for your help.

HI,

Just solved one problem thanks to MrExcel, now I've got another.

I have a list of numbers in cells, the cell format was set as text, I have changed this now to my preferred number setting, but the cells dont pick up in any formula's

I'm sure this is just a little setting somewhere that i need to change.

Thanks in anticipation.

I am trying to add up numbers in cells that have letters in them. My problem
is that there are two types of letters, and I want them to be added in an
adjacent cell. Confusing? Yeah.

OK, I want to add up the numbers from a row into two columns: "A" and "S".
The user will input, say "A8", "S3", and "A4" in one row. I need a formula to
recognize the difference and give an output of "12" in the "A" column, and
"3" in the "S" column.

I've tried VALUE and RIGHT, but it won't let me do a range of cells, or
recognize the difference between "A" and "S".

Thanks

I am trying to add up numbers in cells that have letters in them. My problem
is that there are two types of letters, and I want them to be added in an
adjacent cell. Confusing? Yeah.

OK, I want to add up the numbers from a row into two columns: "A" and "S".
The user will input, say "A8", "S3", and "A4" in one row. I need a formula to
recognize the difference and give an output of "12" in the "A" column, and
"3" in the "S" column.

I've tried VALUE and RIGHT, but it won't let me do a range of cells, or
recognize the difference between "A" and "S".

Thanks

I am using the following formula to obtain a whole number in cell H4:
=IF(C4+0,C4/62+0.5,0)

When I enter a quantity in C4, this then gives me the number of items as a
whole number that I need in I4. I then need to multiply H4 (the whole number,
not any fractional part) x 150 to get an amount in cell D4.

TIA - read below for more info if needed.

(If further explanation will help, C4 is the number of sheets of metal I'm
shipping. Each pallet can hold 61 sheets, so that determines the number of
pallets in H4. I must then multiply the weight of each pallet - 150 - times
the number of pallets. Therefore, fractions won't give me correct info. I
know I can ROUNDUP H4 and multiply x 150, but hope there's an actual formula
that will work as well.)

Typing text into cell, but when complete and click off cell, text does not
appear in cell. Instead a series of pound signs (#) appear in cell. Text
appears in other cells, but this particular cell only shows pound signs when
the cell is not selected.

Please advise! Thank you.

First off I am embarrassed to ask this. I am pretty decent with computers, but I have never EVER used excel in my life. Never had a reason to... until now.
My old lady has been getting on my case to help with budgeting the bills. She however is NOT computer friendly. Tried quicken and that ended up being a total disaster.
I don't want to sound technical, because I am not with excel at all. But the forum rules wants me to put what I have tried so here it is and I am sure I am way off!
I have tried going through the formulas and found an IF formula, but cant seem to figure out how to attach the check box to a specific cell or IF value = - then fill red, or if value = + fill green.

I am trying to make a budget spreadsheet, I am looking for 2 things:

1. A formula that adds numbers in cells ONLY if the check box next to them is checked.
For example I have my expense in column F rows 5-10 and check boxes in column G 5-10.
I want the total (F12) to add only the cells that have a check in the check box corresponding with it in column G. And how do I tie those check boxes to a cell?

2. A formula that fills the box green if the total is in the positive, red if the total is in the negative.

I have been able to find out the basic stuff, but these 2 items seem to be a little far more advanced than my actual comprehension goes.
Any help would be absolutely appreciated.

Matthew

I think there's something blocking simple functions like SUMIF from working in this spreadsheet, and I can't figure out what it might be. I'm not an experienced Excel user, but the formulas in cells B112, B115, B116, for example, seem properly written, but are not returning any usable figures -- the formula itself is just staying there. I've driven myself nuts with this for a week -- anyone have suggestions?

I need some help with code.

I have a button from the Forms toolbar.
If I enter a number in cell C2 "say 1859'
then hit a button...
An input box comes up I have to enter Team number
Once entered, I would find the 1859 in a list of data
Go to the Column with "teams" (Column Q) enter the
input number then hide this row that has the 1859.

If there is a way to reverse this process using code,
that would be great to!!!! Say a 2nd button to unhide
the row and erase the team number.

I would appreciate any help with this code!!
Michael

is there any way to display the row and column number in cell.

A B
1 test
2
3
--------------------------------
A B
1 A1
2
3

thank you for your help

How do I group a record with same info but different number in a row.
Example below:
ID, lname, fname, dob, fallqty,
ID, lname, fname, dob, springqty

How do I write the code to put the column "springqty" on the same line with
the "fallqty" and group the rest of the info since they are all the same(ID,
lname, fname, dob)?

Hi everyone,
I want to have the zero included in my numbers in cells
as example:
Bank account = 032456789
when insert it like 32456789

Thanks

Bakar

I didnt see a previous post that really helped, so I thought I would ask:

I have a form that I print and sent out. I need to add a request number that automatically increases each time it is opened. I dont need to worry about saving off each file individually. I open it, print it, hand fill in some info and send it off.

I want the number in cell J2 to increase by one each time I open it so I dont forget to change it.

How do you calculate when there is text with the number in the cell
example:
a1 value is 26oz
the 26 oz is in the cell
a2 value is $9.50 (cost, only $9.50 is in the cell)
a3 is cost per ounce.

If text is input into some cells referenced by a formula I want the formula
to ignore whatever text it finds and just calculate the result of the numbers
in cells. How do i do this?
Presently the formula displays #VALUE! where there is text in one or more of
the cells referenced.

I wish to enter text into a single cell with a number. This number will be used in a calculation of another cell. Is this possible?

Hi
I need to add a range of numbers in several cells but the problem is
that these cells also contain additional numbers enclosed within
brackets.

For instance how could I add the following:

456(114)
466(78)
325(899)

I only want the total of the first numbers and I want to ignore completely
any numbers within brackets.

Thanks for any help given.

In a spreadsheet I have headings for blood-test values (i.e. RBC, WBC, etc.).
The values below sometimes have text after to indicate the value is (L) -
low, or (VL) - v. low, such as: "23.5 (VL)". Is there a way to use the values
in cells that have text as values, that is to always ignore text and treat
numbers within as numbers. There are many, many other spreadsheets where this
ability w/b very beneficial. Thank you!

A B C D E F
1 0 0 7.75 7.75 7.75

2 LA H

3

4

5 ?

6

For a formula in cell F5, how can I recognize the text "LA", "PD",
or "EA" (or any other text), excluding the text "H". Then, if A2:E2
has the specified text (excluding "H"), to replace the 0 that is
associated with it (from cells A1:E1) with another number (such as 7,
but not putting this number into cells A1:E1), then adding up all of
the cells with 7 and putting this number in cell F5. Also, when adding
up the 7's, the formula should not recognize the 7 from the 0 that is
associated with the text "H".
I know this is an intricate problem and the person who can solve it
is truly an Excel master. I would appreciate any suggestions. Thanks!!

I am trying to get a time sheet (schedule) to work that will calculate mil
time.
I am able to calculate the time nicely but, is there a way to ignore text
that may end up in a cell. Such as SICK or HOL. The string I am currently
use to add up time entered in mil time and convert to hours worked follows.
=(B5>C5)+-C5-B5
I simply need a function that will ignore text entered.
Is this making sense
Thanks in advance Dale

I am trying to get a time sheet (schedule) to work that will calculate mil
time.
I am able to calculate the time nicely but, is there a way to ignore text
that may end up in a cell. Such as SICK or HOL. The string I am currently
use to add up time entered in mil time and convert to hours worked follows.
=(B5>C5)+-C5-B5
I simply need a function that will ignore text entered.
Is this making sense
Thanks in advance Dale

In a spreadsheet I have headings for blood-test values (i.e. RBC, WBC, etc.).
The values below sometimes have text after to indicate the value is (L) -
low, or (VL) - v. low, such as: "23.5 (VL)". Is there a way to use the values
in cells that have text as values, that is to always ignore text and treat
numbers within as numbers. There are many, many other spreadsheets where this
ability w/b very beneficial. Thank you!

Hi,

Is there a way to have text and a number in a cell, and use that cell in a formula, where it picks up the number for the calculation? For example.... cell entry with text would be like --> Est. $3,000.00

Thanks

Hello!

I have a spreadsheet that has Jan-Dec as the column heading. Each month, I enter a number into the row under the month heading. I need a formula that will pick up the number in the current month cell, but ignore any that are empty.

For example, I may have a number of 50 under January, 55 for February, 45 for March, but April thru Dec. are still blank. I need the formula to pick up the number of 45 from March, then the next month, I would need the April number. (I cannot total the 12 months because the number I enter is already the year to date number).

After that I need to divide....
There is a "target" number for the year. I need to take the current month total, divide by the target for the year to get a result to show percentage of how close or far off the number is from the target.

I tried using an "if" statement but Excel only allows up to seven if statements, I need 12.

Help please


No luck finding an answer? You could always try Google.