Parsing Characters

[Jay-Nicolas]

Hi,
I'm sorry if the title isn't correct, I think that's what I'm trying to do is called...
Anywho, I have some XML code that contains a bunch of data that I need to extract and place in another cell / sheet.

The XML code will look something like this:

Please Login or Register  to view this content.

What I need to do is write a macro that can pull things like the account number out of here:

Please Login or Register  to view this content.

The numbers can be in different places throughout this code, and the code is rarely the same, but it follows the same patterns of placing these numbers between labels like that.

I've got agents that have ot extract this information manually and it takes too much time.

If anyone can point me in the right direction (and be a little patient with me :P) I'd really appreciate it.

[mikerickson]

If that is all in A1 and "AccountNumber" (no quotes) is in B1 then
=MID($A$1,FIND(">",A1&">",FIND(B1,A1&B1))+1,FIND("<",A1&"<",FIND(B1,A1&B1))-FIND(">",A1&">",FIND(B1,A1&B1))-1)

will return the string "0001234567890123 "
If you want the trailing space removed, use =TRIM(thatformula).
If you want it as a number rather than a string =VALUE(thatformula)

[MickG]

Hi If data in Column "A"
This will put the A/C Numbers in column "H"

Please Login or Register  to view this content.

Mick

[Jay-Nicolas]

Thanks guys! I'll try them both out and post my results.

[mikerickson]

If they are in a column. Recording a macro that uses Text to Columns, first with delimiter "<" and don't import the first column,
then another Text to Columns with delimiter ">" importing every column and YES, overwritting the cells.

This will give the data format for a VLOOKUP.