Crosstab ordering string
I have a crosstab query where the Column heading is 'Wait: IIf([Clock Stops
in Period Flatfile]![Wait(Weeks)]<0,"0",IIf([Clock Stops in Period
Flatfile]![Wait(Weeks)]>52,"52+",CStr([Clock Stops in Period
I used CStr so that I could have the '52+' criteria, but now that the
returned value is a string, the ordering goes to pot as 6,7,8 and 9 is at the
end of the query.
How can I get the columns ordered, but with '52+' at the end?
You can specify the column headings which will specify the order. You will
have t...What is simple text?
When creating a custom list that includes a column of accounting formated
data, I get an error message that 'fields without simple text will be
ignored'. The result is all other data except the accounting formatted data.
I suppose it means without the currency symbol and thousands delimiters.
On Fri, 8 Apr 2005 11:01:04 -0700, TexMas <TexMas@discussions.microsoft.com>
>When creating a custom list that includes a column of accounting formated
>data, I get an error message that 'fields without simple text will be
>ignored'. The result is ...How to get reference to a range as string?
I can get a reference to a cell as string with function CELL("address",
A1), which will return the string $A$1. How do I get a reference to a
range, for instance, A1:A5?
How will you be using it.
I expect you would have to use a user defined function.
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
<firstname.lastname@example.org> wrote in message news:1105666373.222974.16847...Extracting text from a string
Can anyone help me with a formula to look through a string and find a word
and then add up the number it finds in a column next to the string
containing the word.
eg. find Ric
Col A Col B
So it would report a total for Ric as 3150
Column A and B are on sheet1 and word to search for comes from sheet2.
Where "Ric" is in Sheet2!A1
Return email address is not as DEEP as it appears
"ric" <email@example.com> wr...Money 2004: separate dates for transfers between 2 accounts?
Money requires that you have a single date for a transfer
between 2 accounts. However, in reality, this is often not
For example, when I pay my credit card bill online, the
credit card company uses the date I entered the info
online (e.g., 10/1/2003). However, the money might not
actually be withdrawn from my checking account for a few
days (e.g., 10/6/2003). So, from my credit card
statement's point of view, the transfer occured on 10/1.
However, from my checking statement's point of view, the
transfer occured on 10/6. Money simply uses whichever date
was input la...Unicode-aware String Decorator [new version]
This is the new version of the macro.
It is smarter than first version, in fact it excludes strings in
comments, or string already decorated, or the #include "MyHeader.h"
I've tried to develop the code in a modular way, with classes, so some
part could be reused in other projects/macros.
It seems to work on VS.NET2003; I don't know if it works also in
VS2005 (I don't have it).
I don't know if VS automation exposes the list of all strings in the
document; if such string collection were exposed, a lot of code I have
developed would be useless :) be...Splitting up text in a cell
I have a worksheet extracted from another program with financial data
in it. One of the columns contains information about what money has
been spent on. This consists of a category and a subcategory. For
example one cell might read "Bills : Electricity" or "Going out :
Restaurant". The category and the subcategory are always separated by
one space, a colon, then another space. What I'd like to do it
separate the category from the subcategory by using a formula that can
extract the text either before or after the space-colon-space bit.
Having looked at some of Excel'...How do I delete spaces from the end of text
Some of the text fields that I have to clean up have a space (or more than
one space). I need to clean this text up so the last word is the end of the
Id the text copied from the web, in that case you might have trailing html
characters, if not us
if there are many see:
"George" <George@discussions.microsoft.com> wrote in message
&g...Text fields displayed as scientific Notation
I am current working with Access 2002 and have several reports created... On
both reports I have the same data to display... A number that is stored as
text and can be up to 17 characters long. On one report it displays
correctly, the other it displays as scientific Notation... I've checked all
the fields, and the are define exactly the same... They are in different
positions on the detail line, but have the same input fileds (Text 17
characters)... If the number is less than 17, it displays correctly.. I've
doubled the size of the report field, but doesn't do a...How do I write text on a bar chart using VBA
I have a worksheet with various bar charts. I want to write text inside the
bar but cannot figure out the correct control name to modify the text. Any
help is appreciated
You can add a data label to the data point.
.HasDataLabel = True
.Caption = "Hello"
.Position = xlLabelPositionCenter
.Orientation = xlUpward
'' and more if desired
Peltier Technical Services, Inc.
> ...Repeating numbers amd text ( content of the text box)
Using crosstab query, my report shows like this:
Position Seq No. Position Year 2007
1. Elder Jason
1. Edler Mathew
2. Deacon Mark
2 Deacon John.
How can I make it to show up like this:
1. Elder Jason
2. Deacon Mark
In other word, we do not want it to repeat the same number.
With many thanks,
H. Frank Situmorang
Set the Hide Duplicates property of the Position text box.
Where you also want to...Floating Text Box in Excel 2K3/2K7
I am wanting to create a floating text box in Excel 2003/2007 but am
currently stuck. The idea is that I will be able lock a text box with a few
bullet points in the top right of the screen and that it will remain visible
in that position regardless of where I scroll in the worksheet. Locking cells
instead, or using Headers/Footers is not an option here.
For this purpose, the text box has a name of "Box". I found this code online
(along with several other examples) and replaced the name of the text box
with my own, but could not get it (or any other example) to work. T...convert per cent text to number
in access query:
i have a text field that express percent as ex. :2.5%
I want to convert this to a number. I've tried changing the underlying
table's (a linked table) field type to numeric but get whole/rounded numbers
even when I attempt to express it with multiple decimal places. How can I
convert by using a query?
It is probably already a number! just formatted to show a % FORMAT, but
format is make-up for what is displayed. The "real" stuff is pr0bably
already a number ( a value between 0 and 1, quite likely). What leaves you
on the impression it is not a numbe...Assigning XML string as the content of an attribute
Hi. I want to assign an XML string to an XML attribute. This XML string must
undergo "escape" conversion so that the < and & symbols are converted in to
Does the .Net library have a conversion method that does this? Note that I
want to construct the resulting XML string myself without having to use an
string myXml = "<root><record/><record/></root>";
string myDoc = "<doc myXml='" + Converter.escapeMyXml (myXml ) + "'/>";
...How do I add a " at the end of every cell text...
I have 700 rows which I want to add a " at the beginning of column C
and the end of column D, but when I do =&"""C1 and =D1&""" Excel
Oh yes, I'm then looking to combine them so I have the formula =C1&",
"&D1 so I'll end up with "text, text" in E...
I hope it's going to be something very simple and that I'm not going
to have to do it manually ?! :(
NickTheBatMan formulated the question :
> I have 700 rows which I want to add a " at the beginning of column C
> and the end ...Creating a text only catalog using data from an Excel spreadsheet
I am trying to create a Publisher catalog using data from an Excell spreadsheet. I have been able to use mail merge to format the information but it only lets me view one row of data. The rest are available using a scroll bar. How can I get all of the data to appear at one time?
After managing to set up OE-QuoteFix on his new PC, Ed reads a message
from Gail <firstname.lastname@example.org>...
> I am trying to create a Publisher catalog using data from an Excell
> spreadsheet. I have been able to use mail merge to format the
> information but it only lets me view one r...Office 2003 SP3
I suspect we have a problem comming from the SP3 of Office 2003.
The text import wizzard has some kind of internal logic to dynamically
select the File origin (code page).
For example: for the 2 txt files below it will chose 2 different code pages:
For VED.TXT wizard will choose 1251 Cyrillic (Windows)
For VED2.TXT wizard will choose 1254 Turkish (Windows)
It is not a problem if user opens the txt file manually - s/he than has the
opportunity to specify the code page correctly in case the wizzard has chosen
the wrong one.
The problem comes when other programs (like our on-line b...excel 2000
I recently read a posting about being able to control the tab key t
jump to any specified cell by using a named range... this is great! I
it possible to do the same thing using text boxes
Message posted from http://www.ExcelForum.com
...copying text into XL & text to columns
I have a large text file that I'm wanting to convert to a table. I was
hoping to simply paste it into Excel & then split the fields using Text to
Columns. The problem I'm having is - when I paste the data in, instead of
putting it all into one column (thus allowing me to use Text to Columns), it
pastes it across 9 columns, with no actual logic to how the fields have been
delimited compared to the original file.
Is there any way I can force it to paste how I need it - into just one
Any help greatly appreciated.......Regards, Jason
Have you tried to open it...How to count the occurences of a text string within a cell...
I'm trying to count the number of text-strings, in this case commas,
within a cell.
If I could count 4 commas, I'd know there were (4)+1 objects in the
Thanks in advance. I always research as much as possible before asking
the forum. The forum has ALWAYS come through with the answer!
Message posted from http://www.ExcelForum.com/
or more generic:
&q...TOC removal and Text preserve
How can I remove Word field codes format and highligh, while preserving text
with page numbers that was generated?
What exactly is it that you wish to do?
Do you want to display the field results? - ALT+F9
Do you want to display without the field shading? - Word Options > Advanced
> Show Document Content > Field Shading > When selected (or Never) - In
earlier Word versions the setting is in Tools > Options > View
Do you want to convert the field to text? - Select then CTRL+SHIFT+F9.
<>>< ><<> ><<> <>>< >&...Need formula to Identify Cells that contain both Numbers and Text
I have a spreadsheet that has some cells in a column containing both
text and numbers, i.e. a phone number, city and state. In that same
column I have cells that only contain a city and state. I need to come
up with a formula that will identify which cells do not contain
numbers in them.
If the number is always first then in a column near it (We'll say the
number/text is in column A enter)
Some may be overkill but I add VALUE to make sure it tries to parse the
first character to a...Problem with Connection String in MS office Chart 11.0
I am trying to add a chart on a form in MS Access. (.mdb) I am using Access
2003. Adding MS office chart to my form, I need to define the connection
string. The data I want to use in the chart is stored in the same access
database. What should be the value of the connection string.. If "XX" is the
name of the table containing the data I need to use to plot the chart, what I
should input as value for connection string property and what I should input
as value for commandtext property.
Thanks a lot for your help...
...text not printing
Since the blaster worm hit I have office products that
will not print text items. Does anyone know of a fix?
...How do I get numbers to extend into unfilled cells like text
you know how if you type text into a cell if there is nothing in the
cells next to it it will overflow into those cells so that you can see
all of the text. Why dont numbers work the same way. Even if the
cells are empty I cannot get numbers to overflow into the open cells.
does anyone know what I have to do to get this to work? Merging the
cells is not an option, due to the formula im using.
Message posted from http://www.ExcelForum.com/
I do not know of any way to get the numbers to continue
into an adjacent (even empty) cell. You might try:
that might ...