Do a calculation in cells with text data format
I have a few columns of cells having a mixed data format of number and text.
Is it possible to convert the first row of numbers in text data format for
further calculation? Your guidance to accomplish it is appreciated.
"Ray" <NoSpam-ZQLi@GMail.com> wrote in message
> I have a few columns of cells having a mixed data format of number and
> Is it possible to convert the first row of numbers in text data format for
> further calculation? Your guidance to accomplis...Cell contents-display extends into next cell (???)
Hello, all [=D
What's probably a very simple format-setting has got me baffled....
I was copying some columns & then pasting them (paste special >
values) into a fresh worksheet, in order to get rid of all the tick
marks at the beginning of the cells. The tick marks are gone. But now,
for some reason I can't figure out, in the column's cells on the new
sheet--in a column formatted as General-- when there's too much text to
fit into their own column, it's extending into the neighboring cell.
For example, if Column A is 75 pixels wide, and I happen to have 85
pixel...How can I modify these cells?
I have a column of data that contains "Franklin Fund - Dividend Earned" in
Is there a way to remove the "- Dividend Earned" from each cell without
manually editing individual cells?
MANY years ago there was a spreadsheet called QuattroPro which, IIRC, had a
way to highlight a 'column within a column' and delete it.
Of course I can do it manually but it will be a big time consumer.
There are 10 kinds of people - those who understand binary and those who
"Vic Baron" <firstname.lastname@example.org> wrote i...excel locks up after selecting a cell #2
excel locks up after selecting a cell. When ever, I select a Cell, that will
automatically selects all the cell and this freezes the entire computer.
Can any body who would help me resolve this issue?
...Cell can left indent; anything for right side of cell?
Is there a way to leave more space between the end of a word and the
right side of the column? I indented the left side of the column by
using the indent option set to "1" in the alignment tab of the
Besides adding an extra column to the end of the row and making it
very small in width and extending the border to that extra "spacer"
column, is there a way to make this space? My spreadsheet is done and
if I have to do this, I'll have tons of work re-writing code. I was
going to use Word for the final presentation of the data, but it
didn't work ou...how do I change cell references automatically in formulas
In Excel 2000, I have data in 80 rows and 10 columns. Each week I add a new
row. I have a separate chart for each column with the data range from the
first row to the last.. Each week I have to change the data range to reflect
the new last row for each chart. Is there someway I can do this
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
"jnw3" <email@example.com> wrote in message
news:8A3551F9-FBC6-4841-95B4-618AB1893190@micros...If date in this cell is 3/1/06 then in this cell I want Wednesday
Is this possible?
Say I have a worksheet and I have the date in one cell. With that, would I
be able in another cell, have some type of code that says if cell A1 is this
date, to reference the Windows calender or another function, then place the
actual day in this cell.
right click on mouse
click on format cells
enter dddd in box
this will give you Wednesday
davesexcel's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=31708
View this thread: ...Defining same name for cells in different sheets
Does anyone know the answer to this one?
I want to give the same range name to the same cell
reference in a series of worksheets.
I find I can do this by pre-defining all my range names on
a "master" worksheet and making several copies of the
sheet (try it, it works!)
But let's say I have done this, and entered all my data on
the sheets I have created, and suddenly realize I need
another range name. I haven't found any way to define a
new range name and apply it to the same range on several
This is not the same as a 3-D reference, which I tried. (3-
d referenci...Changing of Cell protections after saving Excel File (2002)
This problem occurs when I protect a document using a
macro 4.0 function: =PROTECT.DOCUMENT(TRUE,,,TRUE,TRUE).
When I use the function within a macro4.0 macro, on an
original file, everything works fine. The sheet has
unlocked cells, and when the sheet is protected, it
allows me to access those cells. But if I save the file,
or save.as another name, then the fun begins.
The enable selection of the sheet( view codes) has gone
from 0-xlNoRestrictions to -4142- xlNoSelection. This
locks me out of doing anything in the sheet. When I
unprotect and then re-protect the sheet using the
T...Alternating cell shading colors for every other merged cell
I am trying to get every other merged row to be a certain cell shading
There is some discussion in the newsgroups about alternating colors
for every row, but in my case I had merged cells and the formulas
given didn't turn out right on my tables since they were based on the
row numbers. Does anyone know how to have alternating cell colors for
merged rows? (the merged rows are random sizes)
Not an answer to your question I'm afraid, but just for info, most people in
here steer clear of merged cells like the plague - they tend to cause far more
problems than they ever se...A
I have a problem with some calculations in a spreadsheet.
I have some numbers that have functions tied to them to figure out the value
I want to show...
I now have other cells H20 = B30
H19 = H15
H23 = h20 - h19.
All cells are showing a currency format. But when you do the subtraction
the H23 shows something different (Looks like a rounding issue).... It is
simple I want to calculate what is shown in the cell. What do I need to do
to do this?
...Can Comments be automatically converted to text cell values?
I have a large number of cells with Comments. I'd like to basically convert
the Comments to text values in cells (in a column right next to the cells
that have the Comments). Is there a function or other tool for doing this in
You can use a User Defined Function to do that.
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"t...Fill Down based on count in another sheet
I am using the following in Excel to copy a formula:
I need to only copy the formula for a certain number of rows up to 2000.
The number of rows I need is based on the number of rows I have in worksheet
"Results Data" in column A. So, if "Results Data" has data in column A2:A16,
I will need to copy down the formula for all columns in worksheet "Results 4"
to column 17.
Dim lStop As Long
With Worksheets("Results Data")
lStop = .Cells(...range where clause??
I am using the range where clause in my code. I just read that this works
only with the SQL Server & not with other databases line Pervasive SQL &
If I use the range start & range end clauses would the code work for all the
I have completed the coding. What would you suggest at this point? Is it
better to recode everything or is there any other solution?
Thanks & Regards,
Since MBS that other databases arn't supported any longer, why would you
need to build code to support it?
s> Hi,...Can I use the grouping and lock and protect cells
I want to be able to make some cells so that they cannot be chaned and also
use the grouping?
Read this from Dave Peterson
If you protect the worksheet in code, you can do more things
Add this to a general module:
.Protect Password:="hi", userinterfaceonly:=True
.EnableOutlining = True
It needs to be reset each time you open the workbook. (excel doesn't remember
it after closing the workbook.)
(you could use the workbook_open even under ThisWorkbook, too.)
I...Selecing cntrl + arrow key to move to next populated cell
This bugs me about excel, and it leads to mistakes, so I thought I'd ask:
When I select Control key plus the arrow key, I thought this skipped all
blank cells and took you to the next populated cell.
What I've found however is that it sometimes ignores cells with content, and
my cursor goes to the bottom of the sheet, leading me to believe that a
column is empty when it is not.
Why does excel do this? does it depend on the data type of the data in the
column? please help, I can't figure it out.
Ctrl-Down (for example)
If the active cell is:
Empty - goes...Changing color of many cells and borders at once
Hello, could someone please help with this
I have one basic template I use, this template has a number of clones
for each of our individual customers with there own comapny colors for
the font and borders. Problem is the base template s always being
improoved, so after a number of changes are done the ss must then be
reporduced for each customer by changing each cell, border, lines etc to
their own company color. I would like to produce some code or procedure
to do this quickly or do once and then just rerun the code each time.
I was thinking the way to do it would be to create the code t...Can you filter or sort by a cell's formatting? #2
Can you filter or sort by the formatting of a cell - these were formatted
manually and do not have conditional formatting applied to them - thanks
You can even use a UserDefined Function that can return the colorindex.
Chip Pearson has a nice function at:
Returning The ColorIndex Of A Cell
If you're new to macros, you may want to read David McRitchie's intro at:
> Can you filter or sort by the formatting of a cell - these were formatted
> manually and do...Counting Consecutive Months in Top Third
Ok. I need to have a formula which would look at a number of columns
and tell me now many 'consecutive' months someone has been in the top
33% of the numbers in that column to date. IE . . .Ann was in the top
33% in Jan and Feb but not in March so in March I would want the
formula to spit out '2', In April I would want it to start over again
at '1' (If in fact she was in the top 33% in March, if not then it
should say '0'). Hardest part in figuring this out (in my head
is how to get it to give me the number as of the current date.
=NON() would ha...change font color of first cell in data table attached to chart
When I change the color of my font in a data table that I attached to my
column chart, the first cell or what appears to be the name for my first
column along the x-axis will not change color. WHY?
>When I change the color of my font in a data table that I
attached to my
>column chart, the first cell or what appears to be the
name for my first
>column along the x-axis will not change color. WHY?
Because Excel does not work with the data cell formatting
for its graphs but only with the data cells volumes...
BTW: Afaik you can not use different colors within the x...How do I count only if either/ or exist
I am attempting to count how many times there is a number greater than zero
in either column A or column B.
My goal is if A=0 and B=0, do not count. If A=0 and B=1, count. If A=1 and
B=1, count only once.
Thank you for any help
> My goal is if A=0 and B=0, do not count.
> If A=0 and B=1, count. If A=1 and B=1,
> count only once.
The "+" functions as "OR" in this context. The OR function cannot be used
in this context; it will not be interpreted a...Linking cells in a worksheet to other worksheets in a workbook
I'd like to be able to link a cell in one worksheet to another worksheet, so
that when I click on the cell in the first worksheet, the other worksheet
opens up. I have a spreadsheet where the first page (worksheet) has a
listing of items contained in other tabs (worksheets). I'd like to be able
to click on the first worksheet "index" and have the appropriate worksheet
pop open with the contents of the index.
Thanks for your responses!
(In Excel 2003) Select a cell to which you would like to add a link. Select
Insert->Hyperlink. Click the "Place in this doc...How do I count like items (words) in a column?
This is probably the dumbest question a person can ask about Excel but I'm
running out of time and it's making me crazy. I have a column with about 20
different items (words not numbers) and some of the items are listed more
than once. All I want to do is create a column that has the total number of
each item in the list. For example:
Where the cars equal 3 and the bikes equal 5 and these numbers are listed
next to the respective item. My goal is to reduce the steps since I have to
do calculate about 20 items for about 30 people.
Any ...How do I change an existing range in a pivot table?
I have new data to a worksheet and I would like the pivot table to include
this data, but I guess when I first created the pivot table the range was set
and now the data is outside this range. How do I change this range?
Right click on the Pivot Table and choose "Wizard".
Click "Back" until you can choose the range again. When you have reselected
it, you can just press "Finish"
Juan Pablo Gonz�lez
"steve" <firstname.lastname@example.org> wrote in message
> I have new...how do I merge 2 cells in excel without loosing data
how to merge two colums?
one contains text rows
the other contains numeric data
without loosing any of the data
this means merging the data also
thanks for your help
Try Debra's nice coverage at her:
She also provides some examples of how to format the numeric data part when
it's combined with text under the "Formatting Examples" section - scroll
down towards the end of the page
GMT+8, 1� 22' N 103� 45' E