Pulling out specific characters
 

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.



--
Louis

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

=RIGHT(A1,10) will pull out the 10 character product ID to a separate
column.

Then, highlight column A and do Edit Replace FindWhat: ///MPS-???-??
will delete that character group from the original column.

Vaya con Dios,
Chuck, CABGx3


"Louis" wrote in message
...
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.



--
Louis

Amazing. Works perfectly.
Thanks

"Ron Rosenfeld" wrote:

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

On Fri, 22 Apr 2005 13:00:03 -0700, "Louis"
wrote:

Amazing. Works perfectly.
Thanks

You're welcome. Thanks for the feedback.


--ron