REPLACE Function

[Guest]

Hello,

We have a spreadsheet that has a Web Query that imports the names of the
states into Column A1 to A50. It imports New York as "New York", however
this does not work for us, as we need it to read "NY". Is there something I
can use to automate this change? Right now we are doing it manually.
The other problem is, New York is not always in the same cell. It could be
in A3 one day and A48 the next. So I'd need a function that searches the
whole column and replaces the occurance of "New York" with "NY".

Thank you.

 

[Guest]

As you are aware the Find / Replace feature can easily change all ocurances
of NEW York to NY in column A.

To automate this, turn on the Macro Recorder, select column A, perform the
Find / Replace, and then turn off the macro recorder. Whenever you refresh
the data you can run the macro and do the replacements automatically.

 

[Guest]

Thanks for the suggestion. I would prefer it, if there were a way to just
have NEW YORK change to NY when the data is brought in during the web query.
Can that be accomplished?

Thanks

 

[Guest]

It is definitely possible to extend the macro to include bring in the web
material, performing general formatting as well as the text conversion. I
have no experience with web queries.

I suggest you create a new posting in the programming section.

 

[Arvi Laanemets]

Hi

Add a sheet with all places, with both full name and short name in it. Like
Sheet City:
FullName ShortName
New York NY
Chicago Chicago
New Orleans NO

Into next column to right of your query table enter the formula like
=VLOOKUP(A1,City!$A$2:$B$100,2,0)
Copy the formula to all rows with returned query data
In query data range properties, set adjacent formulas to be refreshed, and
cells with new data to be overwritten.