I have a column of numbers that are zip codes. Some of the codes are 9
numbers but they are without the hyphen. Example is 523521191. I want to
convert it to 52352-1191
Is there an easy way to do this in Excel XP?
You can use a simple SUBSTITUTE formula. E.g.,
Microsoft MVP - Excel
Pearson Software Consulting, LLC
"Dig" <email@example.com> wrote in message
> I have a column of numbers that are zip codes. So...What happened to Pivot Table "Show Pages"?
What happened to the functionality in Pivot Tables to write a new worksheet
for each value in a Page Field?
I'm sure that it was available in earlier versions of Excel, indeed there
are references to "show pages" in other threads in this group, but I can't
find it in Excel 2003.
Did it go away?
On the Pivot Table toolbar, choose PivotTable > Show Pages
Jim Molter wrote:
> What happened to the functionality in Pivot Tables to write a new worksheet
> for each value in a Page Field?
> I'm sure that it was available in earlier versions of Excel, in...How do I perfom a character count in a single cell in Excel?
I need to peform a character count for certain cells in Excel. I found a
formula for counting words within a cell but not the number of specific
If you count spaces as a character, you can use directly the function LEN.
If you don't want the spaces to count, use something like:
Hope this helps,
> I need to peform a character count for certain cells in Excel. I found a
> formula for counting words within a cell but not the number of specific
...Replacing text with number.
I have attached a copy of part of the worksheet I am working with.
The original sheet has over 35000 rows.
In Column A is the ID#, I would like to replace "DID or DN" (text) in
column B with the ID# in Column A and have it make the change for
Column B when the ID# in a Changes.
Is this possible?
Thanks for any help.
not really sure but why not place the following formula in B1
and copy down for all rows
> I have attached a copy of part of the worksheet I am working wi...Amortization Table
Is there an amortization table in Excel with Office 2000 Professional. If so,
how do I access it? If not where can I get one free. I need to figure
payments for a 3 year loan at 10% interest.
Microsoft has a loan amortization schedule here...
The free Excel add-in "Calculate Payments" could be helpful.
Download from... http://www.realezsites.com/bus/primitivesoftware
No registration required.
San Francisco, USA
"Linda W" <LindaW@discussions.microsoft.com>
wrote in message
Is th..."Unnormalize" table
I have a table which looks like this:
DRG Patient Costs
A a 10
A b 20
A c 15
B a 30
and so on. Now I have to convert it to something like this:
DRG Costs1 Costs2 Costs3
A 10 20 15
Very ugly but I need it this way for Excel.
How can I do that?
> I have a table which looks like this:
> DRG Patient Costs
> A ...setting check box on tables
I have application on access 2003 adp.
To some subForm i have i bound access table in order to make it the
dinamicly way to work.
The table is sql server 2005.
in the table there are bit values. I would like to present them in my table
as checkboxes and not as yes/no or true/false.
Is there a way to so so automaticly?
...Mirrored Page Numbers
I want to have mirrored page numbers -- that is page numbers at the left
margin on even-numbered pages and page numbers at the right margin on
odd-numbered pages. Years ago, I seem to remember doing this quite easily in
PageMaker. Now, I'm using Publisher 2007 running under Windows Vista.
Can Publlisher do this? If so, how?
Thanks in advance for any suggestions.
1.Choose View -> Header and Footer
2.(Optional) In the header, type some identifying text at the blinking
cursor, such as the word Page
3.Click the Insert Page Number button on the Header and...How to import address list tables into Excel
I have an address list in Word that looks like this:
| Mr Smith |
| 34 swanky st. |
| Bufftton 3454-123 |
| UK |
| Fat Bread Co. |
| Attn. Justin Jock |
| 453 Long Drive |
| Staten Island |
| 232434...Pivot Table
How to avoid Zero Sum of Values appearing in a pivot table?
The field items does have data but the amounts cancel each
because they are equal and opposite.
The following code will hide row items with a zero total (Excel 2002):
'hide rows that contain zero totals
'by Debra Dalgleish
Dim r As Integer
Dim i As Integer
Dim pt As PivotTable
Dim pf As PivotField
Dim df As PivotField
Dim pi As PivotItem
Dim pd As Range
Dim str As String
Set pt = Sheets("Pivot").PivotTables(1)
Set df = pt.PivotFields("Units") ...Tricky little combo/table problem
Here's a tricky little problem I am trying to figure out. I'm sure someone
out there should be able to help me without breaking anything. Heres the
problem.... I have a form that has an underlying table attached. I have a
combo box pull in a pull down list of locations from that table. What I would
like to happen is, I would like it so the user will select a location from
that combo box, and then click a command button. When they click that button,
I need whatever they chose in the comobo box to copy over to a NEW table,
which will be referenced later by my program. Say the table being...std::string vs Cstring
I have recently been encouraged to utilise String classes, as opposed to C
type char arrays due to some difficulty debugging some buffer overflows.
I have looked into std::string and CString, but what are the benefits and
The main problem I have is that I do a lot of data formatting using sprintf,
for which I see no substitute using std::string,
On Thu, 30 Dec 2004 23:25:21 -0000, "Mark Randall"
>I have recently been encouraged to utilise String classes, as opposed to C
>type cha...spreading a table over two pages
I am using office 2000. I would like to know how to auto flow tables over
two pages using microsoft publisher. I have a table on the bottom of one
page and it is too large and i want it to automatically spread onto the nest
page in my document. Thanks JJ
You could make the page longer.
"JJ" <JJ@discussions.microsoft.com> wrote in message
>I am using office 2000. I would like to know how to auto flow tables over
> two pages using microsoft publisher. I have a table on the bottom...Refreshing Pivot table
I have a workbook with 6 sheets. Each sheet has a pivot table.
I have a button on another form that runs a query and populates yet another
sheet that each pivot table pulls from.
I want the pivot tables to refresh after the query is run.
I loop through all the sheets and then loop through all pivot tables and
call RefreshTable on each. I've also tried Refresh.
What happens is that first 2 sheets seems to refresh but the others don't.
Also, if there are no records when the pivot tables refresh they reduce down
to just the rows and column (which is normal) b...Count Only Weekday in the DateDiff Function
I have a report where the following function counts the interval of days
between two dates. This is one of the actual formulas being used:
Now, I need to adjust this syntex so that only the weekdays, workdays, are
counted. Please help!
Microsoft Access MVP
> I have a report where the following function counts the interval of days
> between two dates. This is one of the actual formulas being used:
&...Counting rows, then counting values.
I have a lot of spreadsheets with many rows and columns of numbers. I need
a way to find the highest value in each row, (mark it some how, bold the
number maybe), then count the number of "highest" values are in each
Is there an easy way to do this?
You can use this to get the value
and this to count
Regards Ron de Bruin
"Michael via OfficeKB.com" <forum@nospam.OfficeKB.com> wrote in message news:152907df2ac24b6ab6ee8d51d46958d9@OfficeKB.com...
>...How do I add consecutive numbers but start over at break in the.
Recently, the following question was posed by another party:
"How do I add consecutive numbers but start over at a break in them. I'm
trying to calculate consecutive days of attendance. One might have had 20
days in a row missed one and then I need to start over."
The rsvp was:
You received an answer...
In B1 enter:
In B2 enter:
and drag down to copy as needed
My question is:
What does =--(A1>0) mean?
i.e. what is the meaning of...=-- ??
Take a look...Pivot Table Data Source
Where in 2007 can I change the data source for a pivot table to look at the
first pivot table I created (instead of looking directly at the raw data
...Sorting numbers as Text
I frequently work with a lists of text data, some of which consists of digits
or digits + alpha characters. These are always formatted as tect fields.
When I sort them i get a dialog box asking if I would rather sort digits as
numbers or as text. Sorting as text provides the result I need, so I always
select that and it works fine
My question is, how can I skip having to answer this question over and over
and over? Is there a configuration option thats says 'leave me alone - I know
what I am doing' ?
Thanks for any help.
I've never seen the message you refer to - what versio...Substracting numbers in non contigous cells
I need a formula to substract numbers in non contigous cells THAT ARE
IN ONE COLUMN and have the result in other cell, for example:
R11...............insert a table in excel
how do I put a table into excel that stays on screen even when you scroll up
or down i.e what you are scrolling scrolls behind or along side the table
Investigate the use of Window>New Window then Arrange>Vertical to have two
windows side by each.
Your table in one of them and whatever else in the other.
Only other thing I could think of would be a Modeless UserForm or similar.
Gord Dibben Excel MVP
On Thu, 6 Jan 2005 09:43:09 -0800, "Phil1723"
>how do I put a table into excel that stays on screen even when you s...update yes / no field in table based on another table
I am wanting to update a yes/no field in one table based on another table.
If the name is in both tables to put a "yes" value in the field
If the name is in one table but not the other to put a "no" value in the field
Table 1: Name
Table 2: Name
Check box = yes
Table 1: Null (Name not in table)
Table 2: Name
check box = no
On Mon, 3 Mar 2008 14:55:01 -0800, Bryan <Bryan@discussions.microsoft.com>
>I am wanting to update a yes/no field in one table based on another table.
>If the name is in both tables to put a "yes" va...SQL trigger on offline inventory table?
We have our SO configuration set up so that reason codes are required on all
returns. All of our return reason codes are set up so that the items always
get sent to offline inventory first, then the mgr. makes the judgment call on
returning to main, transferring out, etc.
When a transaction that is returned contains both inventory and
non-inventory type items, it assigns the reason code selected to both types
of items, and sends them all to offline inventory. Then when someone returns
a non-inventory item to main inventory or transfers it out, it is changing
the quantity field of the...Affects of cycle count while plant is conntinue to product product
I would like to setup "Count Intervals" in GP 9.0. The actual setup i can do,
I am worried about what will happen if this runs while we are in the middle
of productions order. At this time, we make the production order, then
produce the product, then (most of the time the next day) close out the
production order. My thoughts are that I would run the counts at night, but I
know that not all the production orders will be closed. Will this cause a
problem for process?
...excel changes numbers
Operating System: Mac OS X 10.5 (Leopard)
Processor: Power PC
When I enter a number Excel automatically changes it to a decimal. I enter one, it changes it to 0.01. I have specified the cell format to "number", it is not currency or percentage. Very annoying, I have to enter all my numbers as multiple of 100 to get the right number to display.
As answered just yesterday & numerous times before:
On 3/2/10 3:10 PM, in article 59bb3bcc.-1@webcrossing.JaKIaxP2ac0,