Formula or Macro Needed

[it's an excel world]

Hello all,

I have a report that I export into Excel on a daily basis. One of the columns of data contains a long sting of characters of which I only need a certain part. Each row of data in this column ends with </a>, which I want to drop off and capture all the characters prior to that until I reach the character >. An example is <a href='Default.aspx?PageID=72&PackageHdrID=206066'>1ZV5V4420601084086</a>. All I really want is the 1ZV5V4420601084086.

I wasn't sure if I should use a formula and copy it down the file or create a macro. Please keep in mind that the amount of characters prior to the > is not always the same. But each row does end with </a>. The quantity of rows per export will also very day to day.

Thank you in advance!

 

[VoG]

Try

=LEFT(A1,FIND(">",A1)-1)

 

[it's an excel world]

Sorry about that. I forgot that when I copied the HTML code it would appear as a link to you and not the actual code I am seeing. Each row appears as follows "<a href='Default.aspx?PageID=72&PackageHdrID=206066'>1ZV5V4420601084086</a>". Hopefully it works this time.

Thanks again.

 

[it's an excel world]

Sorry I can't seem to get the HTML code to copy without it creating the link.

 

[VoG]

I guessed at what you have. Does my formula work?

 

[it's an excel world]

That formula is actually bringing back the HTML code itself. The formula dropped the ">" which is what I wanted, but brought back the information prior to it instead of after it. I need the information after it until you reach the </a> at the end. Hopefully that is what I stated at the start and didn't give you bad info.

Thanks again!

 

[VoG]

To return the data after the >

=RIGHT(A1,LEN(A1)-FIND(">",A1))

 

[it's an excel world]

Thank you. That brought back the data after the ">". Is there something that can be added to the formula to drop off the "</a>" at the end?

Thanks again!

 

[VoG]

Try

=RIGHT(A1,LEN(A1)-FIND(">",A1)-2)

 

[it's an excel world]

The -2 actually dropped off the first two characters of what the prior formula was bring back instead of the last four.