Replace more characters using replace

Hi

I have number of characters in a string, that I like to replace with
"nothing". I use a code like this:

Sub test()
    Dim a As String
    a = "Jan+& Per"
    a = Replace(a, " ", "")
    a = Replace(a, "&", "")
    a = Replace(a, "+", "")
    MsgBox a
End Sub


As I have about 15 characters, that all should be replaced with
nothing, I am looking ofr another way to it. Something like

Sub test()
    Dim a As String
    a = "Jan+& Per"
    a = Replace(a, {" ", "&", "+"}, "")
    MsgBox a
End Sub

Is this possible at all?

Jan
0
jkrons
5/18/2010 9:13:31 AM
excel.programming 6508 articles. 2 followers. Follow

3 Replies
857 Views

Similar Articles

[PageSpeed] 35

On May 18, 5:13=A0am, jkrons <j...@knord.dk> wrote:
> Hi
>
> I have number of characters in a string, that I like to replace with
> "nothing". I use a code like this:
>
> Sub test()
> =A0 =A0 Dim a As String
> =A0 =A0 a =3D "Jan+& Per"
> =A0 =A0 a =3D Replace(a, " ", "")
> =A0 =A0 a =3D Replace(a, "&", "")
> =A0 =A0 a =3D Replace(a, "+", "")
> =A0 =A0 MsgBox a
> End Sub
>
> As I have about 15 characters, that all should be replaced with
> nothing, I am looking ofr another way to it. Something like
>
> Sub test()
> =A0 =A0 Dim a As String
> =A0 =A0 a =3D "Jan+& Per"
> =A0 =A0 a =3D Replace(a, {" ", "&", "+"}, "")
> =A0 =A0 MsgBox a
> End Sub
>
> Is this possible at all?
>
> Jan

You can always use a loop:

Sub EasyAsABC()
a =3D "ABCDEFG"
chn =3D Array("A", "B", "C")
For i =3D 0 To UBound(chn)
    a =3D Replace(a, chn(i), "")
Next
MsgBox a
End Sub
0
James
5/18/2010 10:57:24 AM
Of Course!. Thank you.

Jan


On 18 Maj, 12:57, James Ravenswood <james.ravensw...@gmail.com> wrote:
> On May 18, 5:13=A0am, jkrons <j...@knord.dk> wrote:
>
>
>
>
>
> > Hi
>
> > I have number of characters in a string, that I like to replace with
> > "nothing". I use a code like this:
>
> > Sub test()
> > =A0 =A0 Dim a As String
> > =A0 =A0 a =3D "Jan+& Per"
> > =A0 =A0 a =3D Replace(a, " ", "")
> > =A0 =A0 a =3D Replace(a, "&", "")
> > =A0 =A0 a =3D Replace(a, "+", "")
> > =A0 =A0 MsgBox a
> > End Sub
>
> > As I have about 15 characters, that all should be replaced with
> > nothing, I am looking ofr another way to it. Something like
>
> > Sub test()
> > =A0 =A0 Dim a As String
> > =A0 =A0 a =3D "Jan+& Per"
> > =A0 =A0 a =3D Replace(a, {" ", "&", "+"}, "")
> > =A0 =A0 MsgBox a
> > End Sub
>
> > Is this possible at all?
>
> > Jan
>
> You can always use a loop:
>
> Sub EasyAsABC()
> a =3D "ABCDEFG"
> chn =3D Array("A", "B", "C")
> For i =3D 0 To UBound(chn)
> =A0 =A0 a =3D Replace(a, chn(i), "")
> Next
> MsgBox a
> End Sub- Skjul tekst i anf=F8rselstegn -
>
> - Vis tekst i anf=F8rselstegn -

0
jkrons
5/18/2010 11:47:47 AM
 > As I have about 15 characters
 > {" ", "&", "+"}...etc

Hi.  One idea might be to use only 1 string...

Sub YourCode()
     Dim a As String
     a = "Jan+& Per"
     StringReplace a, " &+"
End Sub

Function StringReplace(ByRef Str, s)
     Dim p
     For p = 1 To Len(s)
         Str = Replace(Str, Mid$(s, p, 1), vbNullString)
     Next p
End Function

= = = = = = =
HTH  :>)
Dana DeLouis



On 5/18/2010 7:47 AM, jkrons wrote:
> Of Course!. Thank you.
>
> Jan
>
>
> On 18 Maj, 12:57, James Ravenswood<james.ravensw...@gmail.com>  wrote:
>> On May 18, 5:13 am, jkrons<j...@knord.dk>  wrote:
>>
>>
>>
>>
>>
>>> Hi
>>
>>> I have number of characters in a string, that I like to replace with
>>> "nothing". I use a code like this:
>>
>>> Sub test()
>>>      Dim a As String
>>>      a = "Jan+&  Per"
>>>      a = Replace(a, " ", "")
>>>      a = Replace(a, "&", "")
>>>      a = Replace(a, "+", "")
>>>      MsgBox a
>>> End Sub
>>
>>> As I have about 15 characters, that all should be replaced with
>>> nothing, I am looking ofr another way to it. Something like
>>
>>> Sub test()
>>>      Dim a As String
>>>      a = "Jan+&  Per"
>>>      a = Replace(a, {" ", "&", "+"}, "")
>>>      MsgBox a
>>> End Sub
>>
>>> Is this possible at all?
>>
>>> Jan
>>
>> You can always use a loop:
>>
>> Sub EasyAsABC()
>> a = "ABCDEFG"
>> chn = Array("A", "B", "C")
>> For i = 0 To UBound(chn)
>>      a = Replace(a, chn(i), "")
>> Next
>> MsgBox a
>> End Sub- Skjul tekst i anf�rselstegn -
>>
>> - Vis tekst i anf�rselstegn -

0
Dana
5/18/2010 2:47:48 PM
Reply:

Similar Artilces:

find and replace hyperlink?
Hi, I want to globally find and replace the contents of a hyperlink with a new hyperlink. For example, I want this Jul.xls#'Title Page'!A1 to be replaced with Aug.xls#'Title Page'!A1 I can't seem to find a way do it. Is there a way? Thank you, Mike Take a look at David McRitchie's site: http://www.mvps.org/dmcritchie/excel/buildtoc.htm look for: Fix Hyperlinks (#FixHyperlinks) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm me wrote: > > Hi, I want to g...

Contact use?
I am studying at moment and would appreciate confirmation of below, company has an external consultant who has an internet mail address of Bill@isp.com , company wants to receive all mail addressed to him and do not publish his private internet mail address as they do not want customers to contact him directly, the solution suggested is below. create a mail-enabled contact object for Bill and specify two addresses, Bill@company.com "@company.com being organisation email address" and Bill@isp.com, for this contact. All messages that are addressed to Bill@company.com will fir...

Replace word in sentence function...
I have the following function which replaces a word in a sentence with "....." =REPLACE(I2,SEARCH(E2,I2),LEN(E2),".....") Where the word is E2 and the sentence is I2. At the moment, if the word is "work" and the sentence is "I worked at home" I get the sentence "I .....ed at home". Any ideas how I could capture any remaining letters at the end of the word and get the result "I ..... at home"? I'd greatly appreciate your help. Thanks in advance, Chris can you upload example, would be easier to view -- Message posted from h...

error message when using windows mail
I am trying to set up my aol email account with Windows Mail. I keep getting the following error. I followed the instructions given by the aol website to the letter. I don't know what else to do. Help. The connection to the server has failed. Subject 'test', Account: 'imap.aol.com', Server: 'smtp.aol.com', Protocol: SMTP, Port: 25, Secure(SSL): No, Socket Error: 10013, Error Number: 0x800CCC0E Change the outgoing (SMTP) port number from 25 to 587, as per....... http://about.aol.com/faq/openmailaccess#OE6 These instructions for OE will work for Windo...

Replacing Powerpoint 97 SR-2 on M/S Office 97 Pro Edit on Win XP
I dread asking this in case someone descends on me like a ton of bricks and says the answer is in the posts already! - but i I have never used the PP 97 which came with the MS Office 97 Pro Edit I have installed on my computer which uses Win XP with Ser Pack 3. I am about to embark on using PowerPoint for the FIRST time and wonder whether I should, before anything else, acquire a more up to date version first - and one that will work problem free with my XP - which version should I go for and will it, on loading, overwrite and replace the Powpoint I already have and still operat...

Search and replace problem
Hi, I have an Excel worksheets where in several cell's a symbol was added to mark the cell's "to review". The symbol used was "(*)". I have tried now to delete all the entries with a "search and replace", but Excel interpretes the asterisk as a DOS-symbol and deletes everythng between brackets. Is there a way to avoid that or to let Excel know that I only want to delete the specific characters. TIA Melissa It's because it is a wildcard, you can replace it by preceding it with a tilde ~ which tell Excel to find an asterisk in find what p...

SCOM RMS install account to use
Security wants to see why we cant use a local account to install RMS. We did. Now running setup for other components such as Reporting, which connects back to the RMS, and the RMS cannot verify that the account being used is in the SCOM admins domain security group. Installing RMS with a domain account rather than local account fixes this. Where's the documentation that says you must install with a domain account and cannot use a local account? And if a domain account is used, what happens if that domain account is later deleted - Security is asking ..... Tks, Joe Wrong...

Using Outlook .pst file on Mac
I use a PC now but am getting a mac soon. I have a large database of appointments, contacts, and emails in Outlook from the PC. I know Office 2004 for Mac has Entourage which seems to be a similar item but is not compatible with the pst files from Outlook 2003. As well, I know earlier versions could be converted but since I will still use this on the PC at times I would rather share a file, not convert. is there any 3rd party way of doing this or any other program that would work with these files? MarkW <markwco@RemoveNoSpamcomcast.net> wrote: > I use a PC now but am > gettin...

replace a negative result with a zero?
I have my formula entered but need it to return a zero if the result is negative. Any help is greatly appreciated. -- Pam Well, what is the formula that you are using? You could try this: =3DMAX(your_formula,0) to replace your_formula. Hope this helps. Pete On Apr 19, 5:17=A0pm, Pam2277 <Pam2...@discussions.microsoft.com> wrote: > I have my formula entered but need it to return a zero if the result is > negative. =A0Any help is greatly appreciated. > -- > Pam Hi, =if(your formula<0,0,your formula) "Pam2277" wrote: > ...

Selective Replace in Search and Replace
I need to search and replace in a large document. I don't want to replace the entire search term/expression just part of it. In this case I am searching for a paragraph return followed by any letter (not digit) and then want to change that to paragraph/tab but keep the letter. The search looks like this ^p^$ but naturally if replace is ^p^t I lose the first letter of the text string which I don't want How can I construct either search and replace so that ^p Alexander turns into ^p ^tAlexander and not ^p ^tlexander Thanks in a...

Replace wildcards with a space
I have a text field with all values containing an asterisk. The asterisk can be located anywhere within the contents. I am trying to replace the asterisk with a space. I will then use TRIM to remove any spaces at the beginning of the text. I have been unable to get an update query to work. When my criteria is like "~[*}", Access says that 0 rows will be updated. When the criteria is [*], I am asked to enter a parameter value. Any assistance will be greatly appreciated. Thanks - bkh Barbara wrote: >I have a text field with all values containing an aste...

How do I Use NTBackup from a Non-Exchange 5.5 Computer
How do I use NTBackup from a Non-Exchange 2000 Computer? I'm trying to back up my exchange 5.5 server from a w2k sp3 box. There's a reg hack for exchange 2000 (MS KBA - 275876), but I couldn't find one for 5.5. Can anyone help? Thanks, Mike Mike wrote: > How do I use NTBackup from a Non-Exchange 2000 Computer? > > I'm trying to back up my exchange 5.5 server from a w2k > sp3 box. There's a reg hack for exchange 2000 (MS KBA - > 275876), but I couldn't find one for 5.5. > > Can anyone help? > > Thanks, Mike Hi Mike, have you install...

Using Sum for database data
I have a sql query that imports data from a MySQL database. When the data is brought into Excel I can't sum the numbers. Without all of the details, I'm guessing that the SUM equates to zero. If that is true, the values may be text that appear to be numbers. Try this to correct the problem: 1)Put a 1 in a blank cell and select that cell 2)Edit>Copy 3)Select the imported values 4)Edit>Paste Special --Select Multiply --Select Values Click the [OK] button. Does that resolve the problem? -- Regards, Ron "jnorton" wrote: > I have a sql query that imports data ...

Replace more characters using replace
Hi I have number of characters in a string, that I like to replace with "nothing". I use a code like this: Sub test() Dim a As String a = "Jan+& Per" a = Replace(a, " ", "") a = Replace(a, "&", "") a = Replace(a, "+", "") MsgBox a End Sub As I have about 15 characters, that all should be replaced with nothing, I am looking ofr another way to it. Something like Sub test() Dim a As String a = "Jan+& Per" a = Replace(a, {" ",...

How do I use UML with Visio
I am trying to figure out the way to use UML in visio. I have Visio 2003 SP1 installed in XP? File > New > Software > UML Model Diagram "asafbu" wrote: > I am trying to figure out the way to use UML in visio. I have Visio 2003 SP1 > installed in XP? ... assuming that you have the Pro or EA edition... "wolfv" <wolfv@discussions.microsoft.com> wrote in message news:9246EF85-9ED9-423B-AE96-1A6B0D3D6715@microsoft.com... > File > New > Software > UML Model Diagram > > "asafbu" wrote: > >> I am trying to figure ou...

Replacing old Windows XP and Outlook Express with new Windows 7
How do I export the contents and settings out of Outlook Express and import the accounts, settings and emails into Windows Live Mail on another machine? For messages: Copy the *ENTIRE* OE message store folder to flash or CD. (Folders.dbx must be included). Place this on the Desktop or other location on the machine using WLMail. Open WLMail and: File | Import | Messages | Microsoft Outlook Express 6 and point to where you saved it. For Addresses: Open the Address Book in OE and File | Export | Address Book (wab) and save it to the Desktop. Copy to flash or CD. Place this on the...

sort array of strings using for loop
Hi everybody. I have a problem with sorting an array of names. I have a txt file with a names e.g. "Jack, John, Bob, Adam, Paul". All i have to do is write the names into the individual cells of new array and than sort it by an alphabet using for loop and write it out into a new txt file. I just after figuring out how to separete a names and my source code looks like: string delimiter = ","; string path = @"c:\notsorted.txt"; string r = sr.ReadLine(); string[] names = r.Split(delimiter.ToCharArray()); but i have no idea how to sort it with for ...

have nntp installed, not obvious how to use
can't seem to find anywhere that tells me how to setup oe to use the nntp bridge? Any link available? pjp wrote: > can't seem to find anywhere that tells me how to setup oe to use the > nntp bridge? Any link available? First two Google hits: http://connect.microsoft.com/Microsoftforums/ http://communitybridge.codeplex.com/ https://connect.microsoft.com/site/sitehome.aspx pjp wrote: > can't seem to find anywhere that tells me how to setup oe to use the nntp > bridge? Any link available? http://social.answers.microsoft.com/Forums/en-US/h...

Cannot insert extended ascii character
This article tells about how to insert an extended ascii character: http://office.microsoft.com/en-us/assistance/HA011331361033.aspx The "Alt + decimal code" method works well with my pc at work. But I cannot make that work with my laptop at home (Dell Inspiron 600m, if it matters). Using Office Professional 2003 on Windows XP in both cases. When I type in the code on my laptop, nothing happens. Whether I use the normal laptop number keys, or the ten-key keypad with number lock on, it makes no difference. Can anyone give me any clues? TIA Hi! I don't know why thi...

Opening Excel Attachement Using VBA
Hi I am creating a system that will email a list if people an Excel spreadsheet as an attachment which is personalised to them. The recipient then fills in some data and emails the attachement back to a set email address. At set intervals my system will scan the Exchange folder for new emails. I need to be able to open the attachement and capture the data into an Access database. Most of this I am OK with. The bit I am unsure about is 1. How can I make sure the attachment is an Excel spreadsheet before I try and open it using VBA code? 2. How do I then open the file using VBA? Can anybody pr...

Filter Data using wildcards
I'm trying to filter some data in Excel 97 to only view rows where the description starts with two question marks (eg. ??item1, ??item2 etc). I set up a custom filter with the drop down box set to 'begins with' and the value set to ?? (see attached file) but excel treats question marks as wildcards and thinks the question marks represent any single character. Any idea how to force it to recognise wildcards as actual characters? I presume the same problem occurs if trying to search using the asterisk symbol. Thanks Al File Attached: http://www.exceltip.com/forum/attachment.php...

How to use English Excel formulas in a Spanish Excel?
Hello, Do you know any patch that allows me to use English Excel formulas in my Spanish Excel version? I've Excel 2007. Thanks in advance. Hi, Here is a macro that goes the other way, maybe you can modify it: http://www.mvps.org/dmcritchie/excel/language.htm Or contact the author and ask how it could be modified for you. If this helps, please click the Yes button. -- Thanks, Shane Devenshire "Alex" wrote: > Hello, > > Do you know any patch that allows me to use English Excel formulas in > my Spanish Excel version? > > I've Excel 2007. > &g...

using 2 different IF statements
I have a macro that, after the user fills in 2 different areas of a form, the data in those areas are appended to an Excel list. If either area is not completely filled out, I want a message to appear indicating where the problem is, and the sub exited. I used the following code: If Range("c3") = "" Or Range("c4") = "" Or Range("C5") = "" Or Range("C6") = "" Or _ Range("C7") = "" Or Range("C8") = "" Then answer = MsgBox("Incomplete Header Information", vbInf...

Do you want to replace the contents of
When my macro runs, it stops after this procdure and asks "Do you want to replace the contents ofthe destination cells" is there something I can add that will automatically tell it yes? Here is the part of the macro that it stops on. Selection.TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _ :="/", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)) Thanks H...

Top Link Bar - Use Links From Parent Not Working
I have a master project and 4 subs. I created 4 tabs in the master site that points to all 4 sub sites. When I go into a sub into the Top Link Bar and select "Use Links From Parent", it only adds a tab that exists when you first go into PWA and not all the tabs in the master. What am I doing or not doing that will not propagate the tabs? Thanks, Andy Novak UNT You probably made the 4 sub sites direct children of the top PWA site rather than making them true subsites of the master site. Therefore when they inherit the top link bar of the parent they inheriting the t...