Formatting # in one cell
Can I separate numbers by a comma within a single cell so
I can add, average and use the pivot function calculating
each number separtly? I have the column formatted as
In the cells I have: 2, 3, 4, 5
12, 2, 5, 24
There is one cell per row with the numbers and one column
with these numbers.
Thank you for your help.
You'll have to use Data | Text to columns, prior to doing anything with your
numbers - as they are, Excel will treat them as strings.
MS Excel MVP
"Brent" <email@example.com...Move to match cells
A B C D E
1 000801025-8 009601025-3 2007 000801025-8 2007
2 000801026-9 009601026-4 2004 000801026-9 2004
3 000801028-1 009601028-6 2006 000801028-1 2006
4 000801036-8 009601036-3 2008 000801036-8 2008
5 000801044-5 009601044-0 2007 000801044-5 2007
6 000801044-5 009601055-0 2004 000801055-5 2004
7 000801055-5 009601064-8 2001 000801064-3 2001
8 000801055-5 009601091-2 2006 000801091-7 2006
9 000801064-3 009601095-6 2002 000801095-1 2002
10 000801091-7 009601097-8 1998 000801097-3 1998
11 000801091-7 009...hide columns on a protected sheet???
When using MS Excel 2003: I have turned on "Protect Sheet" to protect some
formulas from being deleted. Prior to turning on protection I unlocked the
cells that I wanted users to be able to edit. And I have checked the box to
"Allow all users of this worksheet to:" "Delete Columns".
What I *don't* see on the "Protect Sheet" menu is an option to "Hide Columns"
Do you know of any way that I can allow users to do this, without turning
off the protection in MS Excel 2003?
Check "Format Columns" option "under...2 axis column chart
Anybody who knows how to make a column chart with 2 different valu
MartinNT's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1565
View this thread: http://www.excelforum.com/showthread.php?threadid=27190
Make the chart with two data series
Right click on one of the data series in the chart; select Format Data
Series, open the Axis tab; click Secondary axis
It needs to data series. It you have only one then you need to reproduce the
values in another column and make a...Cell formatting
I am using a formula to copy cells from one sheet to another.
Everything is ok except the date column.
The when that cell is "" or -0- it shows 1/0/1900
How can I change this to "-" or blank?
In article <firstname.lastname@example.org>,
"seve" <email@example.com> wrote:
> I am using a formula to copy cells from one sheet to another.
> Everything is ok except the date column.
> The when that cell is "" or -0- it shows 1/0...Attach Email to a Cell
Hi, Is there any way to attach emails or other documents to cells?
Try: CTRL+K (Insert > Hyperlink)
> Hi, Is there any way to attach emails or other documents to cells?
Hi, This wont work as I need to attach actual documents to the cell.
"מיכאל (מיקי) אבידן" wrote:
> Try: CTRL+K (Insert > Hyperlink)
> "Dave" wrote:
> > Hi, Is there any way to attach emails or other documents to cells?
> > Thanks
If you say so.....Excel won't highlight selected cells
Hi. Just started having a problem today with selecting cells. I have
worksheets that need to have the formatting changed on certain cells. Some
need to be merged, some need to have different fonts, etc.
Before today, I was able to hold the CTRL key down and select whatever cells
needed to be changed (as a group). I can still do that, but only the Title
row and columns (A,B,C...1,2,3,4...) are highlighted but not the individual
cells that I have clicked on.
The problem is more of a large annoyance and I can still get my work done
but it takes much longer because I can'...format cell to search text in another cell
I'm trying to make it so that when text is typed in a cell, other cells will
read it, and if the text includes certain letters (like "ss" in "dssl" or
"ssdl") then it would insert "N/A" in the appropriate cells. i also want it
to be able to look for more than one thing. Not case sesitive. Below is by no
means code, but i don't know how else to explain it.
For instance in cell C14 (IF C3 = "*ss" or "*bl1" then "N/A" else " ")
=IF(SEARCH("SS", C6,1)>0,"N/A", " "...help importing cell
Hi can someone help me with the formula for importing the value of
from one worksheet to another.
For example the value of cell a1 from worksheet x to current sheet
I believe the formula starts with ~
thanks in advance:
eliostar's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3251
View this thread: http://www.excelforum.com/showthread.php?threadid=52304
You can use something like this:
in the cell to show what is in that cell on your current sheet.
...How suppress chart points
I want to suppress chart points for a cumulative line graph of monthly
telephone costs where the particular month's values have not yet been entered
but there is a formula in the cell which currently returns null but will
return the value once it is posted on the accountmaster sheet.
Use NA(). The will cause the data marker to be omitted. Note the line will
be interpolated between valid data points.
Something like this,
=IF( <test> , <value> , NA() )
Andy Pope, Microsoft MVP - Excel
"sarcastix" <sarcastix@di...How to increase the number of columns.
I need more columns in excel (I now it sounds crazy). If there is no
solution, than how to transform columns in rows?
Please � I�m desperate�����.
istancutu's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35749
View this thread: http://www.excelforum.com/showthread.php?threadid=555288
Nel post news:firstname.lastname@example.org
*istancutu* ha scritto:
> I need more columns in excel (I now it sounds crazy). If there is no
> solution, th...Lock a row at top for titles of columns?
Is there a way to lock the top row that contains the description of columns
> Hi bart-
> Make sure you are in Normal View (_not_ in Print Preview) & go to File>Page
> Setup. On the Sheet page specify what Row(s) to repeat at top.
> HTH |:>)
> "bart dotzman" wrote:
> > Is there a way to lock the top row that contains the description of columns
Make sure you are in Normal View (_not_ in Print Preview) & go to File>Page
Setup. On the Sheet page specify what Row(s) to repe...Formatting cells
I have a seniority roster of personnel. It has five colums and 30 rows. The
borders of the cells are formatted.....some thin some bold. Lets say that
today, there are 15 rows with info in them to represent 15 people . I now
need to move the bottom 5 rows down one row to make room for another person
in proper seniority position. When I cut/paste the 25 cells I lose some of
the formatting and have to go back and insert lines where they have
disappeared. How can I lock the formatting of the border while still being
able to edit the cell content?
Instead of cutting and pasting simp...Merge the same group of cells down a column quickly
I've got 900 rows of data in which each needs to have cells 1:3 merged and
centered (so, a1:a3, b1:b3, c1:c3, etc). Is there any fast way of doing this
besides selecting the same three cells in each row, hitting Merge & Center,
and then moving on to the next row? Trying to create a macro has been
fruitless and quite frustrating.
Im using 2007.
Thanks in advance!
in the name box put a1:c900. then "Merge Across", then r-click selection
and format cells, alignment Horizontal Center.
Rich F wrote:
> I've got 900 rows of data in which ea...Ref cell in another worksheet
Sorry for the very basic question. I have several worksheets in a workbook and have named the first sheet 'Info'. This sheet holds such data as 'contract number' and 'user name'..... This data is required on all the other sheets but i would like to just reference the data from the 'Info' sheet
What do i have to enter on the other sheets so that this info only has to be entered on the 'Info' sheet
How do i reference this data/info from the 'Info' sheet in the header/footer of printed sheets
Thanks for any/all help
Select the sheet...Linking cells to MS Project cells
I have an Excel file with cells linked to MS Project
cells. There are several MS Project source files
involved. When first created all the Excel links worked
as expected. Upon reopening the Excel file some cells
update properly and others display #NAME?. Does anyone
know why and is there a way to fix this?
To make matters a bit more confusing I have seen(on rare
occasion)upon reopening the Excel file some cells
previously displaying the #NAME? value displaying
appropriate linked data correctly.
...Updating Chart Titles from Spreadsheet Cells
I'm using Excel 2002 and I want to be able to have the titles in my
charts update based on entries in the associated spreadsheet. On a
monthly basis I import data into a spreadsheet template that has a
chart associated with it. It would be nice if the title block on the
chart would update based on a cell in the spreadsheet.
Message posted from http://www.ExcelForum.com/
have a look at
> I'm using Excel 2002 and I want to be able to have the titles in my
> charts update based on ...displaying a name in a cell reference
I have two worksheets in an Excel workbook.
Sheet1 has a prize designation column with different prizes i.e. WM20, WM10,
Sheet2 has the prize designation (WM20) matched with a name i.e. Walmart
I need to display the name from sheet2 in a column on sheet1?
You could use a vlookup function to do that for you.
Something like this:
Assuming B6 contains “WM20”
Assuming the range on sheet2 A1:B29 contains WM20 in column A and the
description “Walmart 20 buck Gift certificate etc” in it.
Hope that helps.
Fran...How to refer to current cell in range
This is a simple problem, but after a couple hours of searching Excel
help and Newgroups, I can't seem to find anything helpful.
In Excel 2000
I've been using the countif function to count cells that contain a
certain phrase, e.g. this counts the cells in the range that contain
What I'd like to do is count cells in which the LEN() function returns
a value greater than 12, so something like this:
I just can't seem to find a way to refer to the current cell ...Publisher: How to set up both 2 column and 3 column master?
I want to have both options of a 2 column and 3 column in my newsletter. I
cannot see how to do this. It seems that I have to use only one or the other.
When you create a text box, right-click, format text box, text box tab, columns
button. You can link a two column text box to a three column text box.
Mary Sauer MSFT MVP
"snusanjane" <email@example.com> wrote in message
>I want to have both options of a 2 co...Extract numbers from cell with Text and Numbers
I hope someone can help.
I have a column of cells. Each cell contains some text, and then a
number in parenthases, then a comma, and then come text, and then a
number in parenthases, This repeats probably as many as 5 times,
though some cells have fewer then 5. I want to somehow, if possible,
extract JUST THE NUMBERS. If I can automatically get a total so that I
can average the total (dividing by a number in another column, but in
the same row as the numbers extracted) that would be great. I am most
concerned with not having to manually enter the numbers again.
If anyone ha...Excel: too many different cell formats
I am plagued by this error in a large multi-paged
workbook in Excel 2000. Is there a workaround? Will a
later version have a better tolerance?
Check out this Jack
XL: Error Message: Too Many Different Cell Formats
David McRitchie posted this
Leo Heuser posted a macro 2001-05-06 in programming as a very major revision of the macro in the eee007 article.
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
"Jack" <Hig...Excel 2000
If you use the Tools-Options-View-Rows and Columns, you
can either show or not show the A B C etc. across the
columns and the 1 2 3 in front of the rows. However, I
have a spreadsheet that shows the column headings, but not
the row headings. Anyone got a clue why?
Any chance that both headings are off, but row 1 has been formatted to look like
Any chance that the worksheet window is just off the visible screen? Can you
resize it and make that window fit the screen?
> If you use the Tools-Options-View-Rows and Columns, you
> can either show...How do I get rid of too many active cells in Excel
When I click on a second cell location, the box appeared in the first cell
location remained in the work sheet. I fixed the problem by closing the file
and reopen it but what causes it?
See answer to your next posting of this question.
"Josey" <Josey@discussions.microsoft.com> wrote in message
> When I click on a second cell location, the box appeared in the first cell
> location remained in the work sheet. I fixed the problem by closing the
> and reopen it but what causes it...How can I compare two columns for matches
I have two very long lists, one with 38000 entries. How can I easily compare
them without having to create a unique identifier with a tedious copy paste
Chip Pearson has lots of techniques at:
(some worksheet formulas, some macros)
> I have two very long lists, one with 38000 entries. How can I easily compare
> them without having to create a unique identifier with a tedious copy paste