How could I replace an "=", "-" and "+" sign with space

**Abhushan86** · 05-02-2012, 06:14 AM

Hi,

I have a problem at hand and that is as follows.

I have 4 columns of data. Now I want to check the whole table for "=", "+" and "-" and replace them with a single space, leaving the remaining text after those signs as it is.

Eg., If the earlier text was "+hello Iam on a holiday" or "=Iam on a holiday" then it must get me the result as " Iam on a holiday".

Please find the file attached for help.

regards,
Abhushan.

**zbor** · 05-02-2012, 06:15 AM

Ctrl + H and replace your sign with nothing or space.. what you need.

**tom1977** · 05-02-2012, 06:18 AM

Hi select your data and use CTRL + H with needed combinations

**abousetta** · 05-02-2012, 06:18 AM

Ctrl + H
Find: =
Replace: space (use a space not the work)

Repeated as needed.

**Abhushan86** · 05-02-2012, 06:19 AM

Originally Posted by zbor

Ctrl + H and replace your sign with nothing or space.. what you need.

I would have done that if I had to just use only Excel, but then I need to do that using Macro (I forgot to mention that in the query).

**abousetta** · 05-02-2012, 06:20 AM

Wow... three responses with exactly the same answer. Looks like a record

**abousetta** · 05-02-2012, 06:21 AM

Why a macro? Is it being run on several worksheets in a workbook?

**abousetta** · 05-02-2012, 06:22 AM

Please Login or Register  to view this content.

Just used the macro recorder. Painless I would say.

**Abhushan86** · 05-02-2012, 06:23 AM

Originally Posted by abousetta

Why a macro? Is it being run on several worksheets in a workbook?

I know, but the reason is that the file has to be run automatically as a process of everyday cycle, and there are loads of files (Nearly 100) to be run everyday. Now I guess it is evident that a macro might just beat the rest!

**Abhushan86** · 05-02-2012, 06:40 AM

Iam New to Macro, so I dont understand how to record a macro. It would be great if someone could help me out by posting the code that would run with other pieces of code already done before. I mean to say that I just want a new subroutine for this.

Hope someone could help.

Thanks/

**tom1977** · 05-02-2012, 07:15 AM

try to run this it should replace all signs You mentioned

Please Login or Register  to view this content.

**Abhushan86** · 05-02-2012, 07:39 AM

Originally Posted by tom1977

try to run this it should replace all signs You mentioned

Please Login or Register  to view this content.

Thank you, it works fine, but this checks for the whole string for all the 3 characters, but then if I want to check only in the first character in the cell then is there another way for that?

Regards,
Abhushan.

**abousetta** · 05-02-2012, 12:13 PM

Abhushan, from experience this is probably going to be a smaller part of a bigger code. Could you show us the code that this will fit into so we can just add what you need or else there could be a lot of incremental things to the wish list. As for the macro recorder, it is essential that you learn how to use it. It is available inside of Excel (Developer Tab).

**abousetta** · 05-02-2012, 12:28 PM

This should do what you asked for. Make sure that the cells are formatted as text or you will get an error 2029 message:

Please Login or Register  to view this content.

**Abhushan86** · 05-03-2012, 06:22 AM

Originally Posted by abousetta

This should do what you asked for. Make sure that the cells are formatted as text or you will get an error 2029 message:

Please Login or Register  to view this content.

Hi, the previous code that you had given, worked, but the one that you gave in the end (the one included in this quote), bumped, even after I had converted the whole range to text format.

Anyways thanks for the previous code.

**abousetta** · 05-03-2012, 06:33 AM

I don't know what you mean by "bumped". I tested it from my end and it worked fine. If it's not working for you then upload a sample so we can test it.

**Abhushan86** · 05-03-2012, 07:08 AM

See what I mean to say by bumped is that it is showing the error 2029 in the second code.
The main code is too long, and the main problem lies in this function which is being called from the main subroutine

Please Login or Register  to view this content.

Is it because Iam not mentioning the place where the check for "=", "+", and "-" are to carried out it is causing the error?

If so then please guide me as to how do I declare the place of check in this case.

Thanks
Abhushan.

**abousetta** · 05-03-2012, 10:35 AM

If everything in the worksheet is formatted as text and there are no error messages (you have to clear these first) then this should work for you. I have gone down the pathway of arrays as they are faster for larger ranges. Have a look at the attached workbook.

Please Login or Register  to view this content.

**tom1977** · 05-07-2012, 04:32 AM

Hi test it on data from first post select all cells and run this

Please Login or Register  to view this content.

How could I replace an "=", "-" and "+" sign with space

LinkBack

Thread Tools

Rate This Thread

Display

How could I replace an "=", "-" and "+" sign with space

Re: How could I replace an "=", "-" and "+" sign with space

Re: How could I replace an "=", "-" and "+" sign with space

Re: How could I replace an "=", "-" and "+" sign with space

Re: How could I replace an "=", "-" and "+" sign with space

Re: How could I replace an "=", "-" and "+" sign with space

Re: How could I replace an "=", "-" and "+" sign with space

Re: How could I replace an "=", "-" and "+" sign with space

Re: How could I replace an "=", "-" and "+" sign with space

Re: How could I replace an "=", "-" and "+" sign with space

Re: How could I replace an "=", "-" and "+" sign with space

Re: How could I replace an "=", "-" and "+" sign with space

Re: How could I replace an "=", "-" and "+" sign with space

Re: How could I replace an "=", "-" and "+" sign with space

Re: How could I replace an "=", "-" and "+" sign with space

Re: How could I replace an "=", "-" and "+" sign with space

Re: How could I replace an "=", "-" and "+" sign with space

Re: How could I replace an "=", "-" and "+" sign with space

Re: How could I replace an "=", "-" and "+" sign with space

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions