Pulling out specific characters

[Louis]

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

[Ron Rosenfeld]

On Thu, 21 Apr 2005 16:03:01 -0700, "Louis" <[email protected]>
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

[CLR]

=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" <[email protected]> wrote in message
news:[email protected]...
> 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

[Louis]

Amazing. Works perfectly.
Thanks

"Ron Rosenfeld" wrote:

> On Thu, 21 Apr 2005 16:03:01 -0700, "Louis" <[email protected]>
> 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
>

[Ron Rosenfeld]

On Fri, 22 Apr 2005 13:00:03 -0700, "Louis" <[email protected]>
wrote:

>Amazing. Works perfectly.
>Thanks

You're welcome. Thanks for the feedback.


--ron