Formula to Remove Special Characters
I am trying to figure out a formula that I can use to remove special
characters from Excel 2007 cells. I've used a user function in the
past but can't this time. The Paste special; doesn't cut it either.
Does anyone know of a way to build a formula to remove special
Here's what I've used ion the past that I can't use now in case it
might help some one.
Here's first the formula used to call the function followed by the
=RegExpReplace(EndUsrPhon1,"^ +| +$|\\n|/n| |[^0-9 ]","",TRUE,FALSE)
...about the string literal pool
Does all string literal be stored in the string literal pool. I mean is
there any limitation or once a string literal is declared it will be put in
the pool and stay there until the program stops.
> Does all string literal be stored in the string literal pool. I mean is
> there any limitation or once a string literal is declared it will be put
> in the pool and stay there until the program stops.
Do you see a reason for doing otherwise ? Not me...
It's good to generally question everything but what made you think that
perhaps it co...New line character
I have a table with data over 1 lak records . Few of these records have New
line characters appended to them. Is there any way to remove this new line
Thanks in advance
Message posted via http://www.accessmonster.com
Use an update query with a criterion like this, where XXX is the name of
Right([XXX], 2) = Chr(13) & Chr(10)
and an Update To expression like this
Left([XXX], Len([XXX]) - 2)
In SQL view it's something like
SET XXX = Left([XXX], Len([XXX]) - 2)
WHERE Right([XXX], 2) = Chr(13) & Chr(10)
...Unable to retrieve the complete description string of the event log record
I have an MFC application that reads and displays event log records
using the ::ReadEventLog() API. The problem is with reading the
"Description" message string of the event log record. The MFC
application is unable to read the complete "Description" message
string and displays only some part of it. However the Windows System
Event Log Viewer reads and displays the complete "Description" message
I have ensured that my MFC application reads the entire "Description"
message string by retrieving all the strings as prov...Disclaimer
Hello to everyone...
I have a system with Exchange 2003 (SP1 isn't applied yet) with GFI ME
v.10.1 build 20041008.I have added a disclaimer for both text & html
with English & Greek characters.Then using Outlook 2003 with the
default settings, which means that the encoding used for the outgoing
message it's automatic, I have a problem when there's no Greek
characters in the body of the mail.That has got to do with the
encoding for sure and if I uncheck the automatic encoding and enforce
to use the Greek encoding everything it's ok.But the thing is that I
want a server-b...Any ideas how to copy more than 1024 characters?
I have a spreadsheet where users fill in data and then copy a single cell to
then paste a large paragraph that I have concatenated together using the
input. They are needing to paste the data into various Word and internet
apps. I now needed to add more data into the paragraph to be copied and I
seem to have reached the max characters the cell will display so the paste is
not complete. Any ideas how to work around this with an easier route than
copying and pasting mulitple cells? Thanks!!
You don't need to cpoy/paste the individual cells in an area. Just select
all the cells i...passing boolean variable value into SQL string
I have a class code to insert a record into a table by using SQL (a):
CurrentDb.Execute ("INSERT INTO TBLAGENDA (APPOINTMENT,FIRSTVISIT)
VALUES('" & appointment & "'," & m_firstVisit & ")")
status is a variable from one of the class properties. It is settled form
the user and it is boolean.
What is driving me crazy is that if I run the code above it wont work. I
have to change it this way (b):
Dim status As Integer
If m_firstVisit = True Then
status = -1
status = 0
Curr...Force Users to Change Password at Next Logon
We are setting up Exchange 2003 Ent. Ed. running on Windows 2003 Ent. in a
2 node cluster (A/P). We have a number of remote users who will only access
Exchange via OWA.
We are not upgrading from a previous version of exchange. Due to our
security requirements we need to force users to change their password at
their next logon and at regular intervals (every 90 days). We can access
OWA fine however when we set force user to change password at next logon on
a test user, that test user cannot logon let alone be forced to change their
Garrett Felix, M.S.
An ...Quotation marks in String
Is it possible to have a string variable have a value that contain quotation
dim myStr as string
mystr = "asdf""qwer"
You just double up the quotes.
Or if it really gets confusing:
mystr = "asdf" & chr(34) & "qwer"
> Is it possible to have a string variable have a value that contain quotation
> marks "
or if you want to go blind :-)
mystr = "asdf" & """" & "qwer"
(remove nothere from...String to be split
I have the string in the Excel file where the
"\abc*aa**11111_aa*125.00*125.00*0.0\ " I need to split this data from the
long string stored in the cell. Sometime the above marked string is broken to
next row. how do I split this in macro & to find out when it is split in the
different but continuous row.
Split required are
Col A -111111_aa
Col B -125.00
Thanks & regards,
On 15 dic, 08:07, yagna <ya...@discussions.microsoft.com> wrote:
> I have the string in the Excel file where the =A0
>...removing space from the start of a string
i have a list of a million names to sort. some names have a space at
the beginning and thus appear at the top (A->Z sort) irrespective of
the actual first letter.
how do i check for and remove such spaces?
eg " vista" should be replaced by "vista" and sorted accordingly.
Thanks and regards
Suppose your names are in column A: In a helper column type =TRIM(A1). Copy
and PasteSpecial Values to get rid of the formulas.
"n" <firstname.lastname@example.org> wrote in message
...UNICODE conversion #2
I am downloading one file from web having encoding UTF-8 ( Unicode
Support ). After downloading , I am reading the file into buffer in
CString.But when I saw the buffer I found that there are some
unidentified characters ( Looks like empty spaces but not spaces ). I
am getting the same buffer though I converted it into UNIOCODE using
MultiByteToWideChar( CP_ACP, 0,str,strlen(str)+1, wszStr,256 );
NOw what can I do to see those unidentified characters ? If not
possible , How can I remove those characters ?
Thanks in advance.
> But...Searching for a new line character.
I was wondering if you could help me. I am trying to compare two lists of
numbers to one another and check for anomalies. I can only use excel
functions (no VB scripts :-( ).
I already wrote functions that will examine the data and mark whether the
data has leading zeroes, alpha characters, dashes, etc. However, I am having
difficulty finding a way to check and see if there is a newline character in
the data. Like when someone hit alt+enter to insert a newline in a cell.
How would you use the search function to find it? Or what is the ANSI
character code for a newline char?...Publisher merge
"Publisher can use an address list that contains up to 255
fields. Each field name can contain up to 64 characters".
How do I go about getting to a version of Publisher where
I can increase the number of database fields that
Publisher can handle, and also significantly increase the
no of characters in each field name?
My objective is to merge large financial reports from
Excel to Publisher
No chance at all.
Unlikely to ever be increased from the present limits.
You need to realise the Merge function in Publisher is basic and limited in
Always post the v...Separating text from within string....
My aim is to extract two different strings from one string.
The text is :
=SUMPRODUCT((Category="Sales & Distributions")*(Dept =
And i need to separate out into the "Sales & Distributions" and
"Marketing", and most likely "Apr" as well. i have tried various combos
of InStr, Mid, left etc. I can get the "S..& D.." but stumped on the
Any suggestions would be much appreciated.
Not real robust (specific to the sample you posted)....
To extract "Marketing" (quo...Turning wParam into a character?
I just want to concatenate the character wParam represents into a
character string. For example, if wParam equals the unsigned integer
65 (small letter a), how do I do the equivalent of this?
So that FileName becomes "file a"
United States and British intelligence agencies admittedly break the
law while spying on each other. United States and British governments
claim to energetically share that gathered information.
>I just want to concatenate the character wParam represents into a ...Acknowledge an asterisk as a string
Is there a way to have Access acknowledge an asterisk as a string
value rather than a wildcard? I would like to add validation to a
field that will not allow and asterisk to be added as part of that
field; however, the validation appears to recognize that value as a
wildcard and does not allow any value to be entered.
On May 31, 11:15=A0pm, kidkosmo <kidkos...@yahoo.com> wrote:
> Hi, All,
> Is there a way to have Access acknowledge an asterisk as a string
> value rather than a wildcard? =A0I would like to add validation ...OWA puts additional character into each message
this is weird but I can't figure it out. When I log into OWA and
create a new message it shows simply my signature and so I type my
email and send it and it puts a "?" on the first line of each message.
I thought it was my signature but since it doesn't show up in the new
email until you send it, I ruled out my signature. Then after a bit
more research it seems to be adding the "?" to every email that goes
out via OWA, not though outlook, for every user, not just me. I viewed
the source on the new email and the only "?" in the html are ones that
appear...Random number of characters either side of Literal Characters
I wish to create an input mask that will allow a random number of
characters before and after or between mandatory characters
Example 1. To force the use of XoX followed by 1 or more
characters should allow:
XXL have fun today
Example 2. To force the use of 1 or more characters before XoX
37 abc XoX
Example 3. To force the use of 1 or more characters between YYY
and HHH should allow:
YYYabcd 123 HHH
YYY between HHH
Example 3. To force the use of 1 or more characters before after
and between YYY and HHH shou...> 255 characters in Pivot variable
I created an Excel file with "comments" and some of the cells in Excel have
more than 255 characters.
When I pivot the cell it looks like it truncates at 255. I so wish I could
get all the text in the cell to show up in the pivot. Love the preserve
formatting solutions I read about now all I need is to have my text tha I
have formatte and wrapped in the pivot to show completely.
...Tab character in a multiline text edit field?
Hi! This is probably a stupid, question, but: When creating a dialog with a
multiline text edit field, I am unable to make the field insert the tab
character when I press the tab key, it only selects all text. Or rather, it
wants to go to the next control, but as the edit field is the only
control... well, you know what I mean. This should be a normal text-file
editor. What to do about this?
Hoping for a quick response,
"Morten Aune Lyrstad" <email@example.com> wrote in message
> Hi! This is probably a stupid, question, but: ...vlookup first 4 characters only in a column
Any help with the following would be greatly appreciated -
I'm trying to do a vlookup whereby it only searches/has to match the first 4
characters in the utmost left column of the table and return the associated
cell. My non working formula is:
whereby the A:A(left4) is meant to be the first 4 characters from the left
of the column.
Please keep all correspondence within the Group, so all may benefit!
============...Special Characters #3
Does anybody know how to create special characters such as X-bar or
with a subscript letter? Can it be done in Excel? I am using Exce
version 2003. Thank
Message posted from http://www.ExcelForum.com
Let begin with subscripts: Type x2 in a cell; double click (or use F2) to go
to Edit mode; use the mouse to highlight just the 2 and open the Format
menu; use Format Cell and in the Fonts tab click on subscript. The CTRL,+
trick that works in Word does not work in Excel. The methods shown here can
be modified for superscripts. But it will not work on chart legend text.
For superscri...[O2007] -> How to search for a string in the body and get the line number?
I'm trying to get a reply to a mail message and look for a specific
Then I need to know the position of the line.
search "Sincerely yours,"
get the line number
insert 3 lines BEFORE the "Sincerly Yours"
I can't find a way. I know how to use Replace to change
subtrings, but I can't get a way to just locate one and get
the line number.
How can I do this?
Then, how can I insert text at that linePosition-3?
Thanks for any help.
(Remove the K_I_L_LSPAM from my email address)
I just got OS X and am frustrated because I can't find a shortcut for=20
Greek characters, which I type often in scientific documents. With my=20
old version of Word, the shortcut was Apple-Shift-Q and then the=20
corresponding English letter (a for alpha, etc.). When I try =
Q in Word X, I get the log-out pop-up. I have printed out all the=20
shortcuts and can't find one for Greek characters. Can anyone help? =20
The only solution I can offer is to go to the "Insert" menu and down to
"Symbol". From there, select the symbol character ...