Excel 2007 Change case in a url in a column

Barney Evans
Barney Evans used Ask the Experts™
on
Hi All,

I have a large Excel spreadsheet with URL's to be imported into my web server. I need to change just the display text of the URL's to upper case.

Example

From: <a href="//ctparts.com/catalogsearch/result/?q=ct0201csf" alt="ct0201csf">ct0201csf</a>
To: <a href="//ctparts.com/catalogsearch/result/?q=ct0201csf" alt="ct0201csf">CT0201CSF</a>

Can I do this easily with a formula in a new column?

Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Try this formula...

=LEFT(A1, FIND(">", A2)) & UPPER(MID(A2, FIND(">", A2)+1, FIND("<",A2,2)-FIND(">", A2)-1)) & "</a>"
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
I think there was a typo in the formula suggested by Wayne.
It should be like this....
Assuming your string is in A2 then try this...

=LEFT(A2, FIND(">", A2)) &UPPER(MID(A2, FIND(">", A2)+1, FIND("<",A2,2)-FIND(">", A2)-1)) &"</a>"

Open in new window

Barney EvansIT System Administrator

Author

Commented:
Thanks! Works great!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial