Find and replace 03-04-10
I'm trying to scan a field in one of my tables and find a specific character
and remove it. However, the character is a " so I'm having difficulty. The
field I speak of of contains the sizes of our material so the values look
1/3" and so on.
How can I find all of the " in my feild and remove them? I don't want to
replace them I just want to remove them.
Create a query, and in the Criteria row under the problem field, enter:
Allen Browne - ...eliminate spaces after a text field
I am designing in query and there are spaces after text field. How do I
delete the spaces after? Thanks
Try something like:
To remove trailing spaces only (not leading spaces):
Spaces in the middle of the field will not be affected.
Use your actual field name in place of YourField. Use whatever you like in
place of NewField.
"Cam" <Cam@discussions.microsoft.com> wrote in message
> I am designing in query and there are spaces ...Search and Replace in a column
I can't remember how I did this before and not having much success with my
I just want to do a simple search and replace of all dashes in a field with
Table = Vendor
Column = PhoneNumber
I just want to strip the - (dash) from the field.
What's the easiest way to do this?
SQL Server 2005.
You posted the correct syntax:
SELECT REPLACE(PhoneNumber, '-', '') AS phone
Or if you need to update:
SET PhoneNumber = REPLACE(P...Find and Replace error
I have a worksheet where I want to show only some of the formulas used in
it. That is why, instead of using the option "Show formulas" from the
formulas Tab, I insert a ' before the = in the cells I want to see the
But when I want to show results once again, in order no to look for each
formula in which I added the ', I use the Find and Replace Option. In Find I
specify '= and leave in blank the Replace option. But Excel shows me the
following message: "Excel cannot find the data you are searching for".
Cannot figure out the co...Zip Code Last 4 Digit as Zero
I have a Zip Code Table with only the first 5 digits and do not have the last
How can I make them in 9 digit zip code with the last 4 digit as 0000?.
manually I've been doing this (01234 then add the last digit as -0000)
which come to this 01234-0000.
With a five digit ZIP code in A1, in another cell:
=A1 & "-0000"
Gary''s Student - gsnu201003
> I have a Zip Code Table with only the first 5 digits and do not have the last
> 4 digit.
> How can I make them in 9 digit zip code with the last 4 digit...Chart won't show zero percent
I am doing a pie chart using a quick style design that shows the lines in
between the different percentages. Some of the charts show a percentage with
the lines when the value is zero and two of my charts erase all but one of
the lines when a zero is placed in one of the cells. What's up with that?
and How do I correct it?
Since with zero value the lines of the pie would be on top of each other, I
am not sure what one would expect to see!
Bernard V Liengme
Microsoft Excel MVP
remove caps from email
"PURVIANCE" <PURVIANCE...Find and Replace #6
I have Excel 2002 (with Win 2000 ).
1. Lets say I have 10 cells with the text -> Apple in it. Suppose I choose 6
of these cells which has apple in it and then use the Find feature and type
Apple and use Find All feature then I get a message in message bar at the
bottom of Find saying 6 cells found.
2. Now, I close the find window.I select the 6 cells with Apple again and
now use the replace feature. In "Find what" I type Apple and in "Replace
with" I type Orange and then press Find all, I get the message saying 6
cell(s) found. Now, I press Replace all and the mes...Large Line-Spacing on Replies
When I reply to certain other Outlook user's messages, the spacing between
each line of my reply is enormous. How can I easily reset it to normal line
Easily? That depends on your definition of easily. The only way I know of is
to tweak the text formatting (line spacing, spacing before/after, etc.) as
you would with any other Word document. If there is a one-step process that
actually works, I would sure like to hear about it...
"Bryan Elwood" <firstname.lastname@example.org> wrote in message
&...leading zeros #8
I am creating a column of passwords some of which need to begin with a zero.
When I move to a new cell it automatically drops the initial zero and is
making my 4 digit password 3 numbers. Is tehre a way to keep it set as a
number not text and still elt it hold the inital 0? I am using Excel 2002.
thanks. that worked.
> use a custom number format of 0000
> "P. Zicari" wrote:
> > I am creating a column of passwords some of which need to begin with a zero.
> > When I move to a new cell it automatically drops the ...Prices set to Zero When Exported
Does any one know why my new store db store prices are all at $0.00 after
exporting the store from HQ Administrator?
I have this problem before but when i creat worksheet style250:Update
inventory-Item. It is fix try make it.
"Darcy" <Darcy@discussions.microsoft.com> wrote in message
> Does any one know why my new store db store prices are all at $0.00 after
> exporting the store from HQ Administrator?
It crept into the program during one of their hotfixes. 1.3R fixes it.
Get Secure!...Looking for some advice -- replacing IE with custom app
Our company has been selling a product which relies heavily on the use of
Internet Explorer (IE). But, with the many security issues of the last few
years, IE is becoming more difficult to work with. We are contemplating
replacing our dependency on IE with a new custom application that we would
write. All our current code is written in MSVC/MFC, so this new app would
As we are planning on writing a custom app to replace IE, we are also
looking at adding new custom user interface features that were almost
impossible in the old version of html pages being disp...Is there a way of not showing zero totals in a pivot table?
I have a table with several thousand records and I have created pivot table
within Access 2003 to analyse this data. The amount field is currenlty
formatted as currency with 2 decimal places.
If two records with an account of 123456 and one record with an amount of
500.00 and the other record also with the same account of 123456 and an
amount of -500.00, and so on the pivot table the total is showing as 0.00
(one entry cancelling the other). Does anyone know if there is a way to NOT
show these zeros in the totals column as there are quite a few totals with
zero values which I do...Create Virtual com port zero on wince 6
I am trying to open a virtual comm port (port 0). But i am not able to
Physical com ports are from #1 to #9.
So i need to open port zero as virtual for Bluetooth. It is returning
invalid handle with get last error as 12.
Please find the code snippet for reference. Is there any configuration needs
to be done in BSP for this. Please share your experience.
// Bluetooth.cpp : Defines the entry point for the console application.
#include...replacing an asterisk
I have a huge legacy database that has asterisks in front of thousand
of items. I am trying to get rid of them so that my sort will wor
properly and find that since the * is a wild card I can't do it.
any ideas?? (I KNOW there's gotta be a way but can't think of it at th
Message posted from http://www.ExcelForum.com
oops. from the depths of the brain comes ~* !!
Message posted from http://www.ExcelForum.com
...Macro to hide rows with zero in specifc column range
I have a workbook with multiple sheets. I would like to put together a
macro that will hide the rows in a given range on all sheets. For example,
on Sheet 1 the range of cells I need to evaluate for zero are C9:63. If any
of the cells in the range are zero, I want to hide the corresponding row. So
if C11 is zero, row 11 is hidden. I would repeat the macro for the same
ranges on Sheet 2, 3, 4,...
Any help is appreciated.
For Each sh In ThisWorkbook.Worksheets
For Each c In sh.Range("C9:C63")
If c.Value = 0 Then
c...Lead source best practices
Before getting into my question, I know that there are lots of ways to do
the things I'm asking about. I am just curious what some people consider as
best practices. Hopefully it will lead to a useful conversation for many of
the people on this group.
I am curious what best practices people use for lead sources. Take
something like 'Partner' for example. Do people generally use just
'Partner' and store the partner name somewhere else, or do you build your
lead source list up to include the specific names of the partners you're
doing business with? S...Reduce the size of a sheet with empty space
I have hundred of Excel workbooks with the same space problem.
In the news from 1998, I found this problem with Excel 95/98
-- Original problem (I'm experiencing)
>Excel doesn't seem free up space (memory) when a large portion of a
>is deleted. Other than selecting the active cells and copying to
>sheet, how can I make Excel give up this space?
>XL5, 95, 97 - Highlight all the unused rows and columns, using the
>column labels. Select Edit>Delete... and save the workbook.
>XL5, 95? - close and re-open the workbook.
>To...Replace or Substitute for COMBIN function
I have lots of lines in this format to convert into COMBIN function
(39c3 - 37c3)
In the above line I want to convert it to ((COMBIN(39,3)-(COMBIN(37,3))
I have so many lines in the above format. All I want to change the c to
COMBIN with values intact as I have give the example.
Some of the lines are in this format.
(45c6 / (6c5 x (39c1 - 37c1)))
The above line should become
Thanks in advance.
I assumed you wanted the cells to become a formula. If not, delete the
sTe...Create an empty space within a report
I have a report that I need to create that will print on a very specific
paper. The paper has 2 columns, and in order for the information to fit
properly within the white space of the paper, I need to have a break of 1/2
inch every 2 inches.
The report needs to go down and then across.
I have Access 07, and really need help with this problem.
>I have a report that I need to create that will print on a very specific
>paper. The paper has 2 columns, and in order for the information to fit
>properly within the white space of the paper, I need to have a break of ...Hard drive space for MSCRM 25 users
What is a good hd size for the new MSCRM installation w/ SQL on a box?
There will be 25 users and the company will really only start w/ 25 Account
We have 2 machines - one for CRM one for SQL (Due to problems we were having
with both on the same one)... our CRM has a 36gig and SQL 36gig RAID 5
"Do" <email@example.com> wrote in message
> What is a good hd size for the new MSCRM installation w/ SQL on a box?
> There will be 25 users and the company will really only start w/ 25
A...Replace zero values with blank
I have an excel file with demand data per month. It looks something
like below but has got 20 000 rows instead of one.
1 0 3 12 3 4 0 10 12 10 11 0
The calculations I need to do on each row are: frequency, median and
minimum value. However the calculation needs to be done without zero
values. The zero values needs to be replaced by blanks.
I have tried CTRL + H to replace the zero values. But it gives me the
problem that the zero in 20, 10 etc. gets replaced by blank too.
Does any Excel master know how to change all zeros to blanks in a data
Looking forward on an answer.
/Dan...line spacing in an imported word document
I imported an MS Word (2002) document into Publisher 2002. The Word formating
is lost regarding line spacing. Parapraphs with soft line spacing, i.e. not
using enter to move to the next line, retains the line spacing. But whenver a
line is spaced using enter, or when a blank line is inserted between
paragraphs, the space is twice as large as in the original word document.
This happens without changing the format in Publisher. For my publication,
these spaces are too large. For example, I have a series of short lines
center justified. They need to be in regular paragraph spacing, but bec...Word 2003 view doesn't show space at document top and bottom
I have Word 2003 and Windows 7. All at once, when I open a new document
(Ctrl+n) or sometimes an old existing one, the view, which is in Print
Layout, gets rid of the space at the top and the bottom of the page. I have
to "work around" and click View<Header and Footer<Close. Then it is ok as
long as I have the document open. But when I save and close the document and
open it again, the same thing happens - the space is gone from the top and
I don't know the correct terminology to describe this, but I hope you can
How does this happen,...Replace Co with Company
I have a [CompanyName] field that I need to replace Co with Company
Most of the time Co is at the end of the CompanyName but sometimes it is in
the middle of CompanyName
Ajax Construction Co
Big Construction Co Inc
Be careful. Be very careful. You could use do something as simple, but slow,
as open up the table; click on that column, then do a Find and Replace on
However (this is the careful part) Construction could come out looking like
So make very sure to back up the entire database and that table before doin...Help: How do I remove the space at the end of a number?
This come up with me recently. How do I remove the space at the end of
I'm thinking of using the "Replace" from the Edit menu. I just can't
remember how to do it.
Can someone remember how to it?
I can't add the numbers because of the space.
japorms's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=6544
View this thread: http://www.excelforum.com/showthread.php?threadid=557002
Edit > Replace > type a space in the F...