Replace Wildcard

Hi All, im new here, and working on a macro which filters and sorts an excel spreadsheet.

One of the sub routines it calls searches all the fields, and any text within "quote" is changed to #quote#

Then when the user adds this text to word, they run a replace to change any text within #quote# to become Consolas font.

The way it is done in word is like this:

Replace all using this formula:
Find what: (#)(*)(#)
Click More
Select Use Wildcards
Replace with: \2 (with the cursor in the replace with field)
Click Format
Select Style
Select Code Small Char (this is the consolas font)
Click OK

Ideally i want to do the process in excel instead, to save me the effort, so any text within #quote# is turned into Consolas font... I cant see how this is possible, as the wildcards option doesnt appear to be available, but i am wondering if i am missing something.... I usually do.

Any help you would be gratefully received :

Wow that was quick, thank you. I have already replaced the quotes to #, so I just need to apply the font, I will try what you posted above and let you know. Thanks again.

Thank you. I have tried it. it works as I described, but I wasn't clear...

I only want to change the font style within the #quote# the rest of the font in the cell should remains as is, probably calibri or arial. The end result would be one cell with two font types

I want to remove the ## parts afterwards.

I also want to search the entire worksheet ( but I can change that myself.)


Thank you and sorry I wasn't clear initially.

Initally a cell would look like this

This is a test "some text" and some more words

The end result would be ( I used Courier New below as consoles doesn't exist)


This is a test some text and some more words

Thanks, i will give that a go, I don't understand some of those commands, I will need to google them....

Cool thanks , i will give it a go !

Hi Mumps1,

Thank you it works perfectly. I started trying create a macro to remove the # which is still around the words.

The problem is it resets all the font style formatting.

Please Login or Register  to view this content.

So the font, which was like this:

This is a test some text and some more words, and more in consolas

Becomes

This is a test some text and some more words, and more in consolas

Can you suggest a way to modify the code above so it doesn't reset the formatting... ?

Thanks Mumps1,

That did the trick, now the # is removed but the text isn't reset. Sorry I didn't get back to you sooner.

For anyone that one day stumbles here and wants the final code it is here. It searches the entire worksheet, and looks for any text surrounded with ", changes it to # and then converts the text inside to consolas, then removes the #

Please Login or Register  to view this content.

Hi Mumps1,

I found a bug, in that if only one " is in the spreadsheet around some content, it crashes excel.

I tried to be clever and got myself in a mess trying to write a macro where the excel would be searched, highlight the cell which has only one " and stop.... ( so that it could be fixed by either deleting or adding another ") but thinking about it overnight.... there is simple way.... i just don't know how .....Could you guide me in adding a line that says:

if only one " is found - ignore. Maybe if an odd number of " are in a cell ignore that cell? I cant think of a more efficient way...


Benep123

Beautiful....!!!

Thank you worked like a dream...

Hi, I need some guidance again…
This is the macro I was using… I tested it, and it worked, but now….
These things happen.
• It sometimes adds strange characters such as Â, in spaces.
• It sometimes replaces ‘ in words like doesn’t to doesn’t
• Sometimes it replaces “ with characters like “ and †and does not change the font.
• It sometimes replaces the font with the desired font but leaves the #
• And sometimes it works…
I can’t identify this inconsistency

Please Login or Register  to view this content.

I think one aspect was that when a curly quote is used ” as opposed to the regular quote " the macro doesn't like it.

What confuses me is that the reason the macro changes everything to # before making changes was done to avoid this issue.

Originally Posted by benep123

Initally a cell would look like this

This is a test "some text" and some more words

The end result would be ( I used Courier New below as consoles doesn't exist)


This is a test some text and some more words

See if this is how you wanted.

Please Login or Register  to view this content.

If this doesn't work, need to see you workbook with before/after.

The random characters appear to be from the export, so not a problem with the macro (sorry macro)

I cant attach for some reason... I get a white box, and nothing but the content of my test excel is this - in A1, A2, A3 and A4

Adding text that works "disabled" this should not be a problem.
The word "code code” displays a text but sometimes if the quotes are not the same like before the code “code” breaks and then everything breaks “code some-word” command.
The word "code code” displays a text but sometimes if the quotes are not the same like before the code “code” breaks and then everything breaks “code some-word” command.
The word "code code” displays a text but sometimes if the quotes are not the same like before the code “code” breaks and then everything breaks “code some-word” command.

Which is exactly as the original but different text.... (and shorter) the different " marks seem to cause an issue.

Ok i have attached a dummy sample.

Jindons macro crashes... and the original macro i posted changes the font but leaves the # in place.

Range.Character.Text method only works for the character length up to 256.

Work around

Please Login or Register  to view this content.

Thanks that works , im going to test it. Is there a character limitation with this workaround ?

No, but only concern is when you are applying multiple fonts in the same cell already, it would applies the font you originally set.

It appears to work very nicely. - Thank you thank you. I don't understand what you mean originally set? it appears to manage well with the two font types per cell. (it would never be more than that)

My other issue relates to exporting the text into excel, the encoding type was UTF and it needed to be ANSI to deal with the curly quotes.

Thank you again....

You can output as UTF-8 using ADO.Stream.

I am copying the content to Notepad and saving as ANSI coding - which works (so far)

OK,

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.