Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
=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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
On Fri, 22 Apr 2005 13:00:03 -0700, "Louis"
wrote: Amazing. Works perfectly. Thanks You're welcome. Thanks for the feedback. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run a Macro on Startup for Specific Spreadsheet | Excel Worksheet Functions | |||
Highlight a row if a specific cell is specific numbers/words | Excel Worksheet Functions | |||
How do I make a cell date specific to input a value on a specific. | Excel Discussion (Misc queries) | |||
Hyperlink to specific sheet in Excel Web File | Links and Linking in Excel | |||
pulling characters out of a string | Excel Worksheet Functions |