Remove the character (") from string

[ronbowalker]

Hello all,
I have imported a lot of data from an inventory system and find that some of the descriptions of these inventory items contain the (") characher in them. They were using this character as the inches measurement in the description, and is creating issues in my VB coding. I need to remove these little devils in each occurance......and so here it is to resolve. Where the characted occurs in the string varies, so the plot thickens...

I cannot find this character removal process an any of the posts to this point. And I find it difficult to code this character out creating a variation of any know way.

Here is a sample to be fixed: (16" Tape Measure)&(Vertical Rack 62").

Can anyone shed some light on this?

Thanks in advance....

 

[MrKowz]

Highlight all cells that have this occurance and do a find/replace where you look for the character " and replace with nothing

 

[ronbowalker]

This would work, but I would like to automate this process in VB. There are way too many items in the inventory (which are changing) to scan manually all the time and find them.

Is it possible to do VB?

Thanks

 

[MrKowz]

No need to scan manually. If you want every single " removed from the spreadsheet, do the find/replace, search for " and replace with nothing, then hit "replace all"

If you have a specific column or row that these will always occur in, just highlight that column or row and do the same thing, find/replace.

 

[ronbowalker]

I just ran the macro recorder and got a piece of code to modify and try. I will probably get it to work.

 

[Andrew Fergus]

Hi

In VBA:
NewValue = Worksheetfunction.Substitute(OldValue, """" ," inch")

However, like Kowz I would tend towards pre-cleaning the data using either the menu function or a formula to create a new value in a new column, that is used by the VBA routine:
=SUBSTITUTE(A1,""""," inch")

If you don't want the word inch, then simply use "" to replace the " with nothing.

Andrew