On Thu, 21 Apr 2005 16:03:01 -0700, "Louis"
wrote:

My entire database from QuickBooks combines the categories with the products,
which is fine. I can get the text to look almost exactly how it needs to be
to be uploaded to our e-commerce site; the only thing we need to do is
seperate the characters after the last /// in each cell for the entire column
(which is the actual product ID). Here are a few samples:

SENSORS///Pressure & Vacuum///MPS-1 Basic///MPS-P1G-PC

SENSORS///Pressure & Vacuum///MPS-V2C-NC

The characters at the end are always the product id's which we need
seperated, and have the last "///" removed. So it will look like this:

SENSORS///Pressure & Vacuum///MPS-1 Basic (then in the next column) MPS-P1G-PC

SENSORS///Pressure & Vacuum (then in the next column) MPS-V2C-NC

If I could just extract the the character after the last /// that would be
fine, I could then Text To Column the categories and put them back without
the last /// on the end. But if I could cut that out as well that would save
me a step. With over 15K products this will save a massive amount of time.
Thank you very much for any help.

If your string is in A1:

To get the first column characters:

=LEFT(SUBSTITUTE(A1,"///",CHAR(1),(LEN(A1)-
LEN(SUBSTITUTE(A1,"///","")))/3),FIND(CHAR(1),
SUBSTITUTE(A1,"///",CHAR(1),(LEN(A1)-
LEN(SUBSTITUTE(A1,"///","")))/3))-1)

To get the second column product id:

=MID(SUBSTITUTE(A1,"///",CHAR(1),(LEN(A1)-
LEN(SUBSTITUTE(A1,"///","")))/3),FIND(CHAR(1),
SUBSTITUTE(A1,"///",CHAR(1),(LEN(A1)-
LEN(SUBSTITUTE(A1,"///","")))/3))+1,255)


--ron