| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extracting single piece of data
Quickbooks exports our item list as such:
CIRCUIT CONTROL VALVES- Pneumad:Pressure Regulators:11-Series:Relieving:R11-RK-66 the ":" is the category the item to the right is in. All I need from this is the part # at the end, the R11-RK-66. It will always be at the end of the string. the problem is there are 12K parts, so I can't just "text to column" and go that route, it would take forever. I need a formula or macro I think to take out just the last item after the last ":" A small kicker in this is some items may have 4 categories, some may have 2, some may have 0. Thanks in advance for any ideas... -- Louis |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extracting single piece of data
Very close. It actually works when there are categories before the item, but
for many items there is no category before it, for example, here is a typical couple of rows: 1) AIR JETS- Pneumadyne:Air Jet Kit:AJK-HAN 2) AJB-1 3) CIRCUIT CONTROL VALVES- Pneumad:Pressure Regulators:11-Series:R11-RK-66 4) CIRCUIT CONTROL VALVES- Pneumad:Quick Exhaust:QE11-M-78 The items on the end are the part #'s I need. So the formula worked, I just need something additional for the rows where there is no ":". Many thanks. -- Louis "Ron Coderre" wrote: This returns all of the text after the last occurrence of ":" For a value in A1 B1: =RIGHT(A1,LEN(A1)-LOOKUP(LEN(A1),FIND(":",A1,ROW(INDEX($A:$A,1,1):IN DEX($A:$A,LEN(A1),1))))) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Louis" wrote: Quickbooks exports our item list as such: CIRCUIT CONTROL VALVES- Pneumad:Pressure Regulators:11-Series:Relieving:R11-RK-66 the ":" is the category the item to the right is in. All I need from this is the part # at the end, the R11-RK-66. It will always be at the end of the string. the problem is there are 12K parts, so I can't just "text to column" and go that route, it would take forever. I need a formula or macro I think to take out just the last item after the last ":" A small kicker in this is some items may have 4 categories, some may have 2, some may have 0. Thanks in advance for any ideas... -- Louis |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extracting single piece of data
If Ron's formula works for you in every case except when there is no colon
in the string, then maybe you could just use a Helper column and CONCATENATE a leading colon into every string.....it won't hurt the ones that already have some and will allow the ones that don't to work..... Vaya con Dios, Chuck, CABGx3 "Louis" wrote in message ... Very close. It actually works when there are categories before the item, but for many items there is no category before it, for example, here is a typical couple of rows: 1) AIR JETS- Pneumadyne:Air Jet Kit:AJK-HAN 2) AJB-1 3) CIRCUIT CONTROL VALVES- Pneumad:Pressure Regulators:11-Series:R11-RK-66 4) CIRCUIT CONTROL VALVES- Pneumad:Quick Exhaust:QE11-M-78 The items on the end are the part #'s I need. So the formula worked, I just need something additional for the rows where there is no ":". Many thanks. -- Louis "Ron Coderre" wrote: This returns all of the text after the last occurrence of ":" For a value in A1 B1: =RIGHT(A1,LEN(A1)-LOOKUP(LEN(A1),FIND(":",A1,ROW(INDEX($A:$A,1,1):IN DEX($A:$ A,LEN(A1),1))))) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Louis" wrote: Quickbooks exports our item list as such: CIRCUIT CONTROL VALVES- Pneumad:Pressure Regulators:11-Series:Relieving:R11-RK-66 the ":" is the category the item to the right is in. All I need from this is the part # at the end, the R11-RK-66. It will always be at the end of the string. the problem is there are 12K parts, so I can't just "text to column" and go that route, it would take forever. I need a formula or macro I think to take out just the last item after the last ":" A small kicker in this is some items may have 4 categories, some may have 2, some may have 0. Thanks in advance for any ideas... -- Louis |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extracting single piece of data
that does work, yes. It adds several steps but I've made it work on this
project today. The problem is I will need to do this everyday, and if there was a simple extension to the formula it would decrease the likelyhood of any errors. But this definitely works and I really appreciate the help. Thanks, -- Louis "CLR" wrote: If Ron's formula works for you in every case except when there is no colon in the string, then maybe you could just use a Helper column and CONCATENATE a leading colon into every string.....it won't hurt the ones that already have some and will allow the ones that don't to work..... Vaya con Dios, Chuck, CABGx3 "Louis" wrote in message ... Very close. It actually works when there are categories before the item, but for many items there is no category before it, for example, here is a typical couple of rows: 1) AIR JETS- Pneumadyne:Air Jet Kit:AJK-HAN 2) AJB-1 3) CIRCUIT CONTROL VALVES- Pneumad:Pressure Regulators:11-Series:R11-RK-66 4) CIRCUIT CONTROL VALVES- Pneumad:Quick Exhaust:QE11-M-78 The items on the end are the part #'s I need. So the formula worked, I just need something additional for the rows where there is no ":". Many thanks. -- Louis "Ron Coderre" wrote: This returns all of the text after the last occurrence of ":" For a value in A1 B1: =RIGHT(A1,LEN(A1)-LOOKUP(LEN(A1),FIND(":",A1,ROW(INDEX($A:$A,1,1):IN DEX($A:$ A,LEN(A1),1))))) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Louis" wrote: Quickbooks exports our item list as such: CIRCUIT CONTROL VALVES- Pneumad:Pressure Regulators:11-Series:Relieving:R11-RK-66 the ":" is the category the item to the right is in. All I need from this is the part # at the end, the R11-RK-66. It will always be at the end of the string. the problem is there are 12K parts, so I can't just "text to column" and go that route, it would take forever. I need a formula or macro I think to take out just the last item after the last ":" A small kicker in this is some items may have 4 categories, some may have 2, some may have 0. Thanks in advance for any ideas... -- Louis |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extracting single piece of data
Actually, Chuck is on the right track!
Try this: For a value in A1 (that may or may not have a colon): B1: =RIGHT(A1,LEN(A1)-LOOKUP(LEN(A1),FIND(":",":"&A1,ROW(INDEX($A:$A,1,1 ):INDEX($A:$A,LEN(A1),1))))+1) The formula prepends a colon in the search section of the formula, guaranteeing that a colon will be found. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Louis" wrote: that does work, yes. It adds several steps but I've made it work on this project today. The problem is I will need to do this everyday, and if there was a simple extension to the formula it would decrease the likelyhood of any errors. But this definitely works and I really appreciate the help. Thanks, -- Louis "CLR" wrote: If Ron's formula works for you in every case except when there is no colon in the string, then maybe you could just use a Helper column and CONCATENATE a leading colon into every string.....it won't hurt the ones that already have some and will allow the ones that don't to work..... Vaya con Dios, Chuck, CABGx3 "Louis" wrote in message ... Very close. It actually works when there are categories before the item, but for many items there is no category before it, for example, here is a typical couple of rows: 1) AIR JETS- Pneumadyne:Air Jet Kit:AJK-HAN 2) AJB-1 3) CIRCUIT CONTROL VALVES- Pneumad:Pressure Regulators:11-Series:R11-RK-66 4) CIRCUIT CONTROL VALVES- Pneumad:Quick Exhaust:QE11-M-78 The items on the end are the part #'s I need. So the formula worked, I just need something additional for the rows where there is no ":". Many thanks. -- Louis "Ron Coderre" wrote: This returns all of the text after the last occurrence of ":" For a value in A1 B1: =RIGHT(A1,LEN(A1)-LOOKUP(LEN(A1),FIND(":",A1,ROW(INDEX($A:$A,1,1):IN DEX($A:$ A,LEN(A1),1))))) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Louis" wrote: Quickbooks exports our item list as such: CIRCUIT CONTROL VALVES- Pneumad:Pressure Regulators:11-Series:Relieving:R11-RK-66 the ":" is the category the item to the right is in. All I need from this is the part # at the end, the R11-RK-66. It will always be at the end of the string. the problem is there are 12K parts, so I can't just "text to column" and go that route, it would take forever. I need a formula or macro I think to take out just the last item after the last ":" A small kicker in this is some items may have 4 categories, some may have 2, some may have 0. Thanks in advance for any ideas... -- Louis |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extracting single piece of data
That is UNBELIEVABLE Ron........I stand in awe!.....
Vaya con Dios, Chuck, CABGx3 "Ron Coderre" wrote in message ... Actually, Chuck is on the right track! Try this: For a value in A1 (that may or may not have a colon): B1: =RIGHT(A1,LEN(A1)-LOOKUP(LEN(A1),FIND(":",":"&A1,ROW(INDEX($A:$A,1,1 ):INDEX( $A:$A,LEN(A1),1))))+1) The formula prepends a colon in the search section of the formula, guaranteeing that a colon will be found. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Louis" wrote: that does work, yes. It adds several steps but I've made it work on this project today. The problem is I will need to do this everyday, and if there was a simple extension to the formula it would decrease the likelyhood of any errors. But this definitely works and I really appreciate the help. Thanks, -- Louis "CLR" wrote: If Ron's formula works for you in every case except when there is no colon in the string, then maybe you could just use a Helper column and CONCATENATE a leading colon into every string.....it won't hurt the ones that already have some and will allow the ones that don't to work..... Vaya con Dios, Chuck, CABGx3 "Louis" wrote in message ... Very close. It actually works when there are categories before the item, but for many items there is no category before it, for example, here is a typical couple of rows: 1) AIR JETS- Pneumadyne:Air Jet Kit:AJK-HAN 2) AJB-1 3) CIRCUIT CONTROL VALVES- Pneumad:Pressure Regulators:11-Series:R11-RK-66 4) CIRCUIT CONTROL VALVES- Pneumad:Quick Exhaust:QE11-M-78 The items on the end are the part #'s I need. So the formula worked, I just need something additional for the rows where there is no ":". Many thanks. -- Louis "Ron Coderre" wrote: This returns all of the text after the last occurrence of ":" For a value in A1 B1: =RIGHT(A1,LEN(A1)-LOOKUP(LEN(A1),FIND(":",A1,ROW(INDEX($A:$A,1,1):IN DEX($A:$ A,LEN(A1),1))))) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Louis" wrote: Quickbooks exports our item list as such: CIRCUIT CONTROL VALVES- Pneumad:Pressure Regulators:11-Series:Relieving:R11-RK-66 the ":" is the category the item to the right is in. All I need from this is the part # at the end, the R11-RK-66. It will always be at the end of the string. the problem is there are 12K parts, so I can't just "text to column" and go that route, it would take forever. I need a formula or macro I think to take out just the last item after the last ":" A small kicker in this is some items may have 4 categories, some may have 2, some may have 0. Thanks in advance for any ideas... -- Louis |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extracting single piece of data
I'm happy to "help you off the Railroad Tracks" <g, but Ron's new formula
is "WAY COOL".....I don't have Excel working here tonight to give it a spin, but it sure does look good........let us know how it does. Vaya con Dios, Chuck, CABGx3 "Louis" wrote in message ... that does work, yes. It adds several steps but I've made it work on this project today. The problem is I will need to do this everyday, and if there was a simple extension to the formula it would decrease the likelyhood of any errors. But this definitely works and I really appreciate the help. Thanks, -- Louis "CLR" wrote: If Ron's formula works for you in every case except when there is no colon in the string, then maybe you could just use a Helper column and CONCATENATE a leading colon into every string.....it won't hurt the ones that already have some and will allow the ones that don't to work..... Vaya con Dios, Chuck, CABGx3 "Louis" wrote in message ... Very close. It actually works when there are categories before the item, but for many items there is no category before it, for example, here is a typical couple of rows: 1) AIR JETS- Pneumadyne:Air Jet Kit:AJK-HAN 2) AJB-1 3) CIRCUIT CONTROL VALVES- Pneumad:Pressure Regulators:11-Series:R11-RK-66 4) CIRCUIT CONTROL VALVES- Pneumad:Quick Exhaust:QE11-M-78 The items on the end are the part #'s I need. So the formula worked, I just need something additional for the rows where there is no ":". Many thanks. -- Louis "Ron Coderre" wrote: This returns all of the text after the last occurrence of ":" For a value in A1 B1: =RIGHT(A1,LEN(A1)-LOOKUP(LEN(A1),FIND(":",A1,ROW(INDEX($A:$A,1,1):IN DEX($A:$ A,LEN(A1),1))))) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Louis" wrote: Quickbooks exports our item list as such: CIRCUIT CONTROL VALVES- Pneumad:Pressure Regulators:11-Series:Relieving:R11-RK-66 the ":" is the category the item to the right is in. All I need from this is the part # at the end, the R11-RK-66. It will always be at the end of the string. the problem is there are 12K parts, so I can't just "text to column" and go that route, it would take forever. I need a formula or macro I think to take out just the last item after the last ":" A small kicker in this is some items may have 4 categories, some may have 2, some may have 0. Thanks in advance for any ideas... -- Louis |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extracting single piece of data
Chuck
What's really awesome is the collaboration in these groups. The base formula I posted is a Harlan enhanced version of a formula I posted a while back. *********** Best Regards, Ron XL2002, WinXP-Pro "CLR" wrote: That is UNBELIEVABLE Ron........I stand in awe!..... Vaya con Dios, Chuck, CABGx3 "Ron Coderre" wrote in message ... Actually, Chuck is on the right track! Try this: For a value in A1 (that may or may not have a colon): B1: =RIGHT(A1,LEN(A1)-LOOKUP(LEN(A1),FIND(":",":"&A1,ROW(INDEX($A:$A,1,1 ):INDEX( $A:$A,LEN(A1),1))))+1) The formula prepends a colon in the search section of the formula, guaranteeing that a colon will be found. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Louis" wrote: that does work, yes. It adds several steps but I've made it work on this project today. The problem is I will need to do this everyday, and if there was a simple extension to the formula it would decrease the likelyhood of any errors. But this definitely works and I really appreciate the help. Thanks, -- Louis "CLR" wrote: If Ron's formula works for you in every case except when there is no colon in the string, then maybe you could just use a Helper column and CONCATENATE a leading colon into every string.....it won't hurt the ones that already have some and will allow the ones that don't to work..... Vaya con Dios, Chuck, CABGx3 "Louis" wrote in message ... Very close. It actually works when there are categories before the item, but for many items there is no category before it, for example, here is a typical couple of rows: 1) AIR JETS- Pneumadyne:Air Jet Kit:AJK-HAN 2) AJB-1 3) CIRCUIT CONTROL VALVES- Pneumad:Pressure Regulators:11-Series:R11-RK-66 4) CIRCUIT CONTROL VALVES- Pneumad:Quick Exhaust:QE11-M-78 The items on the end are the part #'s I need. So the formula worked, I just need something additional for the rows where there is no ":". Many thanks. -- Louis "Ron Coderre" wrote: This returns all of the text after the last occurrence of ":" For a value in A1 B1: =RIGHT(A1,LEN(A1)-LOOKUP(LEN(A1),FIND(":",A1,ROW(INDEX($A:$A,1,1):IN DEX($A:$ A,LEN(A1),1))))) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Louis" wrote: Quickbooks exports our item list as such: CIRCUIT CONTROL VALVES- Pneumad:Pressure Regulators:11-Series:Relieving:R11-RK-66 the ":" is the category the item to the right is in. All I need from this is the part # at the end, the R11-RK-66. It will always be at the end of the string. the problem is there are 12K parts, so I can't just "text to column" and go that route, it would take forever. I need a formula or macro I think to take out just the last item after the last ":" A small kicker in this is some items may have 4 categories, some may have 2, some may have 0. Thanks in advance for any ideas... -- Louis |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Return Single Row of Numeric Data to Single Column | Excel Worksheet Functions | |||
| Excel Macro to Copy & Paste | Excel Worksheet Functions | |||
| From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) | |||
| extracting data from a spreadsheet by searching on columns | New Users to Excel | |||
| Line Graph Data Recognition | Charts and Charting in Excel | |||
Linear Mode
