Pulling out specific characters

[Guest]

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.

 

[Ron Rosenfeld]

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

 

[Guest]

Amazing. Works perfectly.
Thanks

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]

Amazing. Works perfectly.
Thanks

You're welcome. Thanks for the feedback.


--ron