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
1175 Views

Similar Articles

[PageSpeed] 2

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:

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 ...

Find and replace deleting the rest of the text
I am using Microsoft Publisher for coupons and they are dated and numbered. My problem is that when I use the Find and Replace option to change The month/year, it deletes the rest of the coupon. I have no idea what "protection" was deleted and need to know if there's a remedy to update all the couponsat once instead of having to update each one individually. What version Publisher? What template are you using? Are you using a merge? Can you change the information before you merge without the deletion? -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.m...

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, {" ",...

XLfit4..have you used it?
Hi All I have been reading the suppliers blurb on a graphing extension for excel called XLfit4 http://www.adeptscience.co.uk/products/dataanal/xlfit/) which sounds pretty good ("research strength curve fitting") and would probably get around some of Excel's limitations, thus avoid having to copy/paste data and/or switch back and forth between 2 separate programs. Or could it be a case of Excelfit4 nothing? Has anyone used it (its pretty new, I think), or one of the previous versions? If so, please offer an opinion, particularly regarding Excel integration and general ease-of-us...

Creating macro for find and replace
I recorded a macro for find value 0 and replace with nothing. However after I run it on another excel worksheet, there is an error. Run-time error 91: Object variable or with block variable not set. Is there any way to rectify this? Thank you for your help hidaya, It would help if you would post your code. HTH, Bernie MS Excel MVP "hidaya" <anonymous@discussions.microsoft.com> wrote in message news:225d001c45cca$f74af350$a001280a@phx.gbl... > I recorded a macro for find value 0 and replace with > nothing. However after I run it on another excel > worksheet, there...

How to use "Distributed(Indent)" ?
There is an option of "Distributed(Indent)" for Format Cells/Alignment. I experimented but it did exactly the same as "Center". Does anyone know what else "Distributed(Indent)" can do other than centering? Try putting a few words in the cell. Distributed indent spreads the words across the cell. Experiment with it. Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "churin" <churin@new.postalias> wrote in message news:extcFQgkIHA.3780@TK2MSFTNGP06.phx.gbl... > There is an option of "Dis...

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...

Help with counter using SetDlgItemInt
I've created a dialog-based program using MFC. The program converts an ASCII report file to a tab-delimited file for import into Excel. I want to put a counter on the main dialog that is updated each time a line is read from the input file. I tried this: SetDlgItemInt(IDC_LINE_COUNT,iCountLines); but I get a stack overflow. Any suggestions? Thanks. ben On Thu, 03 Jul 2003 11:35:06 -0600, atDFN wrote: >SetDlgItemInt(IDC_LINE_COUNT,iCountLines); > >but I get a stack overflow. Any suggestions? I don't see how doing that could possibly generate a stack overflow. I t...

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...

Look For Should Use the Current View
There are many times that the users are trying to use the Look For to search for items that are Inactive or Resolved. My biggest problem is with the Case, we get calls on Resolved Cases and the only solution is to scroll page-by-page of the Resolved Cases or to modify an Advanced Find...both are time consuming with a customer on the line. What would be ideal would be for the "Look For" to go against the current view. This would solve many problems my users are having on multiple entities. Or you could add an additional item "Filter" that performs similar to the &...

Checking characters , non-printable etc.
I'm copying some data from another spreadsheet that someone else created. It looks fine on the screen, but when I print it or paste it into Outlook, it gets messed up. Specifically, there is one cell which has about 20 lines of text. Some of the lines begin with a bullet character. When it prints some of the bullets are at the beginning of the lines and some show up near the end of the line. Effectively, the format of the text is not what we're trying to achieve. In Word, I can show spaces and paragraph marks. Is there a way to do that in Excel. I could write a for...

How do I recover document replaced by accident?
How do I recover document replaced by accident? If you don't have a backup I'm afraid you are sol. -- Don Guillett SalesAid Software donaldb@281.com "fortyfive" <fortyfive@discussions.microsoft.com> wrote in message news:6F78FE34-F135-4FE3-A460-42CE167A9A89@microsoft.com... > How do I recover document replaced by accident? If you have a backup you can start there. If on a network, perhaps your IT people back everything up at intervals? If no backup.......no chance. Gord Dibben Excel MVP On Thu, 24 Feb 2005 06:39:03 -0800, fortyfive <fortyfive@discussion...

How can I combine multiple characters into a single character?
I want to create a new character which is a result of combining multiple charaters together into a single character. I remember there was a way in EXCEL that I can do that. However, I can no longer find that instruction from EXCEL Help anymore. Overstriking characters can be done in MS Word but not in Excel. "Yukon Chin" wrote: > I want to create a new character which is a result of combining multiple > charaters together into a single character. I remember there was a way in > EXCEL that I can do that. However, I can no longer find that instruction from > EXCE...

VBA to find filename and replace
Hi I just found the answer to "Find and Replace in VBA" and tried to adapt it for what I'm doing, but it's more difficult so I need some help please. I have multiple worksheets in an excel 2007 workbook - a monthly report. Within the worksheets I have many cells that link to last months workbooks - (i.e I compare last month figures to this months figures). I need the macro to do the following: - Create an Input box for the user to "enter the name ofTHE OLD months workbook" - Create an Input box for the user to "enter the name of THE NEW mont...

Replacing Character
I need to replace/delete the " >" character at the end of a number... when the data was brought into the excel 1234> was the number that cam it happens in about 7000 lines any suggestions on how to replace it with a blank?? thank -- tico31p ----------------------------------------------------------------------- tico31pl's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1040 View this thread: http://www.excelforum.com/showthread.php?threadid=46572 Select the range to fix. Edit|Replace what: " >" (without the quotes with: (leav...

creating join with only the first 4 characters of a field
Anyone can help in creating this join in designer? the best way to join on just 4 characters is to do it in the SQL view. If you want to do it in the query design view (the grid). You can build a query on the first table with the calculated field Field: JoinThis: Left(SomeField,4) Save that query Now build a query on the second table doing the same thing Now build a query with the two saved queries as the sources and join on the two calculated fields. Alternative Build the query on the two tables and join on the fields that you need. Switch to SQL view and find the clause TableA INN...

Replacing A Column Of Numbers
Hello, New user of Excel 2007, and Excel in general. Should be simple, but I just can't figure out how. Have a column of numbers in Column A In Column B (row 1)I have the formula of what's in A (row 1) multiplied by 1000 And I have dragged this formula downwards, so I now have in each row B what is in the same row in Column A multiplied by 1000. So far, all is fine. Now, I want to replace what's in Column A by the newly generated values in Column B (row by row replacement) I can't just Cut out everything in Column A, as B then disappears, as, I'v...

limitation of characters in query?
I am trying to write a condition in my query using IIf statement and the statement is quite long. But I facing a problems that I am not able to type out all the condition in the query. Is there any limits for character type in the query field? On 9 Apr 2007 19:10:02 -0700, xiaodan86@hotmail.com wrote: >I am trying to write a condition in my query using IIf statement and >the statement is quite long. >But I facing a problems that I am not able to type out all the >condition in the query. >Is there any limits for character type in the query field? Yes: 1024 bytes if I recall a...

How to use IN and Sub query?
Greeting, Anyone can explain for me the Using of IN and Sub query? I have the following situation I need to List the customers who bought dogs in the first quarter and also bought dog food in the fourth quarter using IN and Sub query My tables are as follows: Table Animal has AnimalID Category Table Customers CustomerID Phone Name Table Sales CustomeriD SaleDate SaleID Table Saleanimal SaleID AnimalID SalePrice Table SaleItem SaleiD ItemID Table Merchandise Item ID Description Guessing at relationships based on your table and field names. SELEC...

How to extract and use data?
I do excel sheet for gallery with listing of artwork and prices. Is there a way to pull off the data for title, artist, medium, and price to create cards to print out for posting on wall by the paintings? I have MS Office Professional Edition 2003. I may be in the wrong newsgroup now but I appreciate any direction. Thanks, Susan Hi Susan you may provide some more details :-) - some example data of your current entries (plain text - no attachments please) - based on these examples the desired splitting - some information (rules) how to identify the different sub strings -- Regard...

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...

Help using indirect addressing in chart series
Hi, I have named a single cell "DataCount" and a complete column "DataRange". In DataCount i have the count(DataRange) function. I would like to use DataCount when specifying the series range in a chart. ActiveChart.SeriesCollection(1).Values = "='System 4'!R2C13:R35C13" In this example I need to substitute R35 with DataCount. What is the correct syntax? Thanks in advance, val = range("DataCount").Value ActiveChart.SeriesCollection(1).Values = "='System 4'!R2C13:R" & val & "C13" "G...

Using Code instead of Criteria
In my query builder, I'm getting the message: "The string returned by the builder is too long. The result will be truncated." I think I understand why, as I am using a form to specify a large number of options for the report. Can I use VB to specify these criteria instead of the query builder? Examples in query builder: If [Forms]![boxSeatsMailings]![PW] Criteria =1 and (PFDirect>0 or PFBulk>0 or PIDirect>0) then select record. If [Forms]![boxSeatsMailings]![PW] Criteria =2 and (PFDirect>0 or PFBulk>0 or PIDirect>0) then select record. As ind...

why use std:: ?
I always thought using standards template library was for compatibility with other OS'es.. But I see alot of MFC coders use <vector> and why are they not using MFC collections? Lisa Pearlson wrote: > I always thought using standards template library was for compatibility with > other OS'es.. > But I see alot of MFC coders use <vector> and why are they not using MFC > collections? > > That is like saying that using C++ is for compatibility with other OS'es. After all, the STL is a part of the C++ language. When you are as old as I you will kno...

Peculiar search and Replace
I wonder if anyone can help me with the following search and replace. I want to replace the character string CO2 with the equivalent string except with the 2 subscripted. I tried highlighting the 2 in the replacement string and then selecting special formating and choosing a subscripted font, but that just resulted in the entire string being subscripted. I am using Word 2007 Format the string as desired in your document and copy it to the Clipboard, then use ^c (Clipboard contents) as your "Replace with" text. -- Suzanne S. Barnhill Microsoft MVP (W...