Quickbooks exports our item list as such:
CIRCUIT CONTROL VALVES- Pneumad:Pressure
Regulators:11-Series:Relieving:R11-RK-66
the quot;:quot; 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 quot;text to columnquot; 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 quot;:quot;
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
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 quot;:quot;.
Many thanks.
--
Louisquot;Ron Coderrequot; wrote:
gt; This returns all of the text after the last occurrence of quot;:quot;
gt; For a value in A1
gt;
gt;
gt; B1:
gt; =RIGHT(A1,LEN(A1)-LOOKUP(LEN(A1),FIND(quot;:quot;,A1,ROW(INDEX($A:$A,1,1):IN DEX($A:$A,LEN(A1),1)))))
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Louisquot; wrote:
gt;
gt; gt; Quickbooks exports our item list as such:
gt; gt;
gt; gt; CIRCUIT CONTROL VALVES- Pneumad:Pressure
gt; gt; Regulators:11-Series:Relieving:R11-RK-66
gt; gt;
gt; gt; the quot;:quot; is the category the item to the right is in.
gt; gt;
gt; gt; All I need from this is the part # at the end, the R11-RK-66. It will
gt; gt; always be at the end of the string. the problem is there are 12K parts, so I
gt; gt; can't just quot;text to columnquot; and go that route, it would take forever. I need
gt; gt; a formula or macro I think to take out just the last item after the last quot;:quot;
gt; gt; A small kicker in this is some items may have 4 categories, some may have 2,
gt; gt; some may have 0.
gt; gt; Thanks in advance for any ideas...
gt; gt;
gt; gt; --
gt; gt; Louis
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, CABGx3quot;Louisquot; gt; wrote in message
...
gt; Very close. It actually works when there are categories before the item,
but
gt; for many items there is no category before it, for example, here is a
typical
gt; couple of rows:
gt;
gt; 1) AIR JETS- Pneumadyne:Air Jet Kit:AJK-HAN
gt; 2) AJB-1
gt; 3) CIRCUIT CONTROL VALVES- Pneumad:Pressure Regulators:11-Series:R11-RK-66
gt; 4) CIRCUIT CONTROL VALVES- Pneumad:Quick Exhaust:QE11-M-78
gt;
gt; The items on the end are the part #'s I need. So the formula worked, I
just
gt; need something additional for the rows where there is no quot;:quot;.
gt;
gt; Many thanks.
gt;
gt; --
gt; Louis
gt;
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; This returns all of the text after the last occurrence of quot;:quot;
gt; gt; For a value in A1
gt; gt;
gt; gt;
gt; gt; B1:
gt; gt;
=RIGHT(A1,LEN(A1)-LOOKUP(LEN(A1),FIND(quot;:quot;,A1,ROW(INDEX($A:$A,1,1):IN DEX($A:$
A,LEN(A1),1)))))
gt; gt;
gt; gt; Does that help?
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;Louisquot; wrote:
gt; gt;
gt; gt; gt; Quickbooks exports our item list as such:
gt; gt; gt;
gt; gt; gt; CIRCUIT CONTROL VALVES- Pneumad:Pressure
gt; gt; gt; Regulators:11-Series:Relieving:R11-RK-66
gt; gt; gt;
gt; gt; gt; the quot;:quot; is the category the item to the right is in.
gt; gt; gt;
gt; gt; gt; All I need from this is the part # at the end, the R11-RK-66. It will
gt; gt; gt; always be at the end of the string. the problem is there are 12K
parts, so I
gt; gt; gt; can't just quot;text to columnquot; and go that route, it would take forever.
I need
gt; gt; gt; a formula or macro I think to take out just the last item after the
last quot;:quot;
gt; gt; gt; A small kicker in this is some items may have 4 categories, some may
have 2,
gt; gt; gt; some may have 0.
gt; gt; gt; Thanks in advance for any ideas...
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; Louis
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,
--
Louisquot;CLRquot; wrote:
gt; If Ron's formula works for you in every case except when there is no colon
gt; in the string, then maybe you could just use a Helper column and CONCATENATE
gt; a leading colon into every string.....it won't hurt the ones that already
gt; have some and will allow the ones that don't to work.....
gt;
gt; Vaya con Dios,
gt; Chuck, CABGx3
gt;
gt;
gt; quot;Louisquot; gt; wrote in message
gt; ...
gt; gt; Very close. It actually works when there are categories before the item,
gt; but
gt; gt; for many items there is no category before it, for example, here is a
gt; typical
gt; gt; couple of rows:
gt; gt;
gt; gt; 1) AIR JETS- Pneumadyne:Air Jet Kit:AJK-HAN
gt; gt; 2) AJB-1
gt; gt; 3) CIRCUIT CONTROL VALVES- Pneumad:Pressure Regulators:11-Series:R11-RK-66
gt; gt; 4) CIRCUIT CONTROL VALVES- Pneumad:Quick Exhaust:QE11-M-78
gt; gt;
gt; gt; The items on the end are the part #'s I need. So the formula worked, I
gt; just
gt; gt; need something additional for the rows where there is no quot;:quot;.
gt; gt;
gt; gt; Many thanks.
gt; gt;
gt; gt; --
gt; gt; Louis
gt; gt;
gt; gt;
gt; gt; quot;Ron Coderrequot; wrote:
gt; gt;
gt; gt; gt; This returns all of the text after the last occurrence of quot;:quot;
gt; gt; gt; For a value in A1
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; B1:
gt; gt; gt;
gt; =RIGHT(A1,LEN(A1)-LOOKUP(LEN(A1),FIND(quot;:quot;,A1,ROW(INDEX($A:$A,1,1):IN DEX($A:$
gt; A,LEN(A1),1)))))
gt; gt; gt;
gt; gt; gt; Does that help?
gt; gt; gt;
gt; gt; gt; ***********
gt; gt; gt; Regards,
gt; gt; gt; Ron
gt; gt; gt;
gt; gt; gt; XL2002, WinXP-Pro
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Louisquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Quickbooks exports our item list as such:
gt; gt; gt; gt;
gt; gt; gt; gt; CIRCUIT CONTROL VALVES- Pneumad:Pressure
gt; gt; gt; gt; Regulators:11-Series:Relieving:R11-RK-66
gt; gt; gt; gt;
gt; gt; gt; gt; the quot;:quot; is the category the item to the right is in.
gt; gt; gt; gt;
gt; gt; gt; gt; All I need from this is the part # at the end, the R11-RK-66. It will
gt; gt; gt; gt; always be at the end of the string. the problem is there are 12K
gt; parts, so I
gt; gt; gt; gt; can't just quot;text to columnquot; and go that route, it would take forever.
gt; I need
gt; gt; gt; gt; a formula or macro I think to take out just the last item after the
gt; last quot;:quot;
gt; gt; gt; gt; A small kicker in this is some items may have 4 categories, some may
gt; have 2,
gt; gt; gt; gt; some may have 0.
gt; gt; gt; gt; Thanks in advance for any ideas...
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt; Louis
gt;
gt;
gt;
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(quot;:quot;,quot;:quot;amp;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-Proquot;Louisquot; wrote:
gt; that does work, yes. It adds several steps but I've made it work on this
gt; project today. The problem is I will need to do this everyday, and if there
gt; was a simple extension to the formula it would decrease the likelyhood of any
gt; errors. But this definitely works and I really appreciate the help.
gt;
gt; Thanks,
gt; --
gt; Louis
gt;
gt;
gt; quot;CLRquot; wrote:
gt;
gt; gt; If Ron's formula works for you in every case except when there is no colon
gt; gt; in the string, then maybe you could just use a Helper column and CONCATENATE
gt; gt; a leading colon into every string.....it won't hurt the ones that already
gt; gt; have some and will allow the ones that don't to work.....
gt; gt;
gt; gt; Vaya con Dios,
gt; gt; Chuck, CABGx3
gt; gt;
gt; gt;
gt; gt; quot;Louisquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; Very close. It actually works when there are categories before the item,
gt; gt; but
gt; gt; gt; for many items there is no category before it, for example, here is a
gt; gt; typical
gt; gt; gt; couple of rows:
gt; gt; gt;
gt; gt; gt; 1) AIR JETS- Pneumadyne:Air Jet Kit:AJK-HAN
gt; gt; gt; 2) AJB-1
gt; gt; gt; 3) CIRCUIT CONTROL VALVES- Pneumad:Pressure Regulators:11-Series:R11-RK-66
gt; gt; gt; 4) CIRCUIT CONTROL VALVES- Pneumad:Quick Exhaust:QE11-M-78
gt; gt; gt;
gt; gt; gt; The items on the end are the part #'s I need. So the formula worked, I
gt; gt; just
gt; gt; gt; need something additional for the rows where there is no quot;:quot;.
gt; gt; gt;
gt; gt; gt; Many thanks.
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; Louis
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Ron Coderrequot; wrote:
gt; gt; gt;
gt; gt; gt; gt; This returns all of the text after the last occurrence of quot;:quot;
gt; gt; gt; gt; For a value in A1
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; B1:
gt; gt; gt; gt;
gt; gt; =RIGHT(A1,LEN(A1)-LOOKUP(LEN(A1),FIND(quot;:quot;,A1,ROW(INDEX($A:$A,1,1):IN DEX($A:$
gt; gt; A,LEN(A1),1)))))
gt; gt; gt; gt;
gt; gt; gt; gt; Does that help?
gt; gt; gt; gt;
gt; gt; gt; gt; ***********
gt; gt; gt; gt; Regards,
gt; gt; gt; gt; Ron
gt; gt; gt; gt;
gt; gt; gt; gt; XL2002, WinXP-Pro
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Louisquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; Quickbooks exports our item list as such:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; CIRCUIT CONTROL VALVES- Pneumad:Pressure
gt; gt; gt; gt; gt; Regulators:11-Series:Relieving:R11-RK-66
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; the quot;:quot; is the category the item to the right is in.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; All I need from this is the part # at the end, the R11-RK-66. It will
gt; gt; gt; gt; gt; always be at the end of the string. the problem is there are 12K
gt; gt; parts, so I
gt; gt; gt; gt; gt; can't just quot;text to columnquot; and go that route, it would take forever.
gt; gt; I need
gt; gt; gt; gt; gt; a formula or macro I think to take out just the last item after the
gt; gt; last quot;:quot;
gt; gt; gt; gt; gt; A small kicker in this is some items may have 4 categories, some may
gt; gt; have 2,
gt; gt; gt; gt; gt; some may have 0.
gt; gt; gt; gt; gt; Thanks in advance for any ideas...
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; Louis
gt; gt;
gt; gt;
gt; gt;
That is UNBELIEVABLE Ron........I stand in awe!.....
Vaya con Dios,
Chuck, CABGx3quot;Ron Coderrequot; gt; wrote in message
news
gt; Actually, Chuck is on the right track!
gt;
gt; Try this:
gt;
gt; For a value in A1 (that may or may not have a colon):
gt; B1:
gt;
=RIGHT(A1,LEN(A1)-LOOKUP(LEN(A1),FIND(quot;:quot;,quot;:quot;amp;A1,ROW(INDEX($A:$A,1,1 ):INDEX(
$A:$A,LEN(A1),1)))) 1)
gt;
gt; The formula prepends a colon in the search section of the formula,
gt; guaranteeing that a colon will be found.
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Louisquot; wrote:
gt;
gt; gt; that does work, yes. It adds several steps but I've made it work on
this
gt; gt; project today. The problem is I will need to do this everyday, and if
there
gt; gt; was a simple extension to the formula it would decrease the likelyhood
of any
gt; gt; errors. But this definitely works and I really appreciate the help.
gt; gt;
gt; gt; Thanks,
gt; gt; --
gt; gt; Louis
gt; gt;
gt; gt;
gt; gt; quot;CLRquot; wrote:
gt; gt;
gt; gt; gt; If Ron's formula works for you in every case except when there is no
colon
gt; gt; gt; in the string, then maybe you could just use a Helper column and
CONCATENATE
gt; gt; gt; a leading colon into every string.....it won't hurt the ones that
already
gt; gt; gt; have some and will allow the ones that don't to work.....
gt; gt; gt;
gt; gt; gt; Vaya con Dios,
gt; gt; gt; Chuck, CABGx3
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Louisquot; gt; wrote in message
gt; gt; gt; ...
gt; gt; gt; gt; Very close. It actually works when there are categories before the
item,
gt; gt; gt; but
gt; gt; gt; gt; for many items there is no category before it, for example, here is
a
gt; gt; gt; typical
gt; gt; gt; gt; couple of rows:
gt; gt; gt; gt;
gt; gt; gt; gt; 1) AIR JETS- Pneumadyne:Air Jet Kit:AJK-HAN
gt; gt; gt; gt; 2) AJB-1
gt; gt; gt; gt; 3) CIRCUIT CONTROL VALVES- Pneumad:Pressure
Regulators:11-Series:R11-RK-66
gt; gt; gt; gt; 4) CIRCUIT CONTROL VALVES- Pneumad:Quick Exhaust:QE11-M-78
gt; gt; gt; gt;
gt; gt; gt; gt; The items on the end are the part #'s I need. So the formula
worked, I
gt; gt; gt; just
gt; gt; gt; gt; need something additional for the rows where there is no quot;:quot;.
gt; gt; gt; gt;
gt; gt; gt; gt; Many thanks.
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt; Louis
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Ron Coderrequot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; This returns all of the text after the last occurrence of quot;:quot;
gt; gt; gt; gt; gt; For a value in A1
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; B1:
gt; gt; gt; gt; gt;
gt; gt; gt;
=RIGHT(A1,LEN(A1)-LOOKUP(LEN(A1),FIND(quot;:quot;,A1,ROW(INDEX($A:$A,1,1):IN DEX($A:$
gt; gt; gt; A,LEN(A1),1)))))
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Does that help?
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; ***********
gt; gt; gt; gt; gt; Regards,
gt; gt; gt; gt; gt; Ron
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; XL2002, WinXP-Pro
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; quot;Louisquot; wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Quickbooks exports our item list as such:
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; CIRCUIT CONTROL VALVES- Pneumad:Pressure
gt; gt; gt; gt; gt; gt; Regulators:11-Series:Relieving:R11-RK-66
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; the quot;:quot; is the category the item to the right is in.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; All I need from this is the part # at the end, the R11-RK-66.
It will
gt; gt; gt; gt; gt; gt; always be at the end of the string. the problem is there are
12K
gt; gt; gt; parts, so I
gt; gt; gt; gt; gt; gt; can't just quot;text to columnquot; and go that route, it would take
forever.
gt; gt; gt; I need
gt; gt; gt; gt; gt; gt; a formula or macro I think to take out just the last item after
the
gt; gt; gt; last quot;:quot;
gt; gt; gt; gt; gt; gt; A small kicker in this is some items may have 4 categories, some
may
gt; gt; gt; have 2,
gt; gt; gt; gt; gt; gt; some may have 0.
gt; gt; gt; gt; gt; gt; Thanks in advance for any ideas...
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; gt; Louis
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
I'm happy to quot;help you off the Railroad Tracksquot; lt;ggt;, but Ron's new formula
is quot;WAY COOLquot;.....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
quot;Louisquot; gt; wrote in message
...
gt; that does work, yes. It adds several steps but I've made it work on this
gt; project today. The problem is I will need to do this everyday, and if
there
gt; was a simple extension to the formula it would decrease the likelyhood of
any
gt; errors. But this definitely works and I really appreciate the help.
gt;
gt; Thanks,
gt; --
gt; Louis
gt;
gt;
gt; quot;CLRquot; wrote:
gt;
gt; gt; If Ron's formula works for you in every case except when there is no
colon
gt; gt; in the string, then maybe you could just use a Helper column and
CONCATENATE
gt; gt; a leading colon into every string.....it won't hurt the ones that
already
gt; gt; have some and will allow the ones that don't to work.....
gt; gt;
gt; gt; Vaya con Dios,
gt; gt; Chuck, CABGx3
gt; gt;
gt; gt;
gt; gt; quot;Louisquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; Very close. It actually works when there are categories before the
item,
gt; gt; but
gt; gt; gt; for many items there is no category before it, for example, here is a
gt; gt; typical
gt; gt; gt; couple of rows:
gt; gt; gt;
gt; gt; gt; 1) AIR JETS- Pneumadyne:Air Jet Kit:AJK-HAN
gt; gt; gt; 2) AJB-1
gt; gt; gt; 3) CIRCUIT CONTROL VALVES- Pneumad:Pressure
Regulators:11-Series:R11-RK-66
gt; gt; gt; 4) CIRCUIT CONTROL VALVES- Pneumad:Quick Exhaust:QE11-M-78
gt; gt; gt;
gt; gt; gt; The items on the end are the part #'s I need. So the formula worked,
I
gt; gt; just
gt; gt; gt; need something additional for the rows where there is no quot;:quot;.
gt; gt; gt;
gt; gt; gt; Many thanks.
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; Louis
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Ron Coderrequot; wrote:
gt; gt; gt;
gt; gt; gt; gt; This returns all of the text after the last occurrence of quot;:quot;
gt; gt; gt; gt; For a value in A1
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; B1:
gt; gt; gt; gt;
gt; gt;
=RIGHT(A1,LEN(A1)-LOOKUP(LEN(A1),FIND(quot;:quot;,A1,ROW(INDEX($A:$A,1,1):IN DEX($A:$
gt; gt; A,LEN(A1),1)))))
gt; gt; gt; gt;
gt; gt; gt; gt; Does that help?
gt; gt; gt; gt;
gt; gt; gt; gt; ***********
gt; gt; gt; gt; Regards,
gt; gt; gt; gt; Ron
gt; gt; gt; gt;
gt; gt; gt; gt; XL2002, WinXP-Pro
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Louisquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; Quickbooks exports our item list as such:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; CIRCUIT CONTROL VALVES- Pneumad:Pressure
gt; gt; gt; gt; gt; Regulators:11-Series:Relieving:R11-RK-66
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; the quot;:quot; is the category the item to the right is in.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; All I need from this is the part # at the end, the R11-RK-66. It
will
gt; gt; gt; gt; gt; always be at the end of the string. the problem is there are 12K
gt; gt; parts, so I
gt; gt; gt; gt; gt; can't just quot;text to columnquot; and go that route, it would take
forever.
gt; gt; I need
gt; gt; gt; gt; gt; a formula or macro I think to take out just the last item after
the
gt; gt; last quot;:quot;
gt; gt; gt; gt; gt; A small kicker in this is some items may have 4 categories, some
may
gt; gt; have 2,
gt; gt; gt; gt; gt; some may have 0.
gt; gt; gt; gt; gt; Thanks in advance for any ideas...
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; Louis
gt; gt;
gt; gt;
gt; gt;
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-Proquot;CLRquot; wrote:
gt; That is UNBELIEVABLE Ron........I stand in awe!.....
gt;
gt; Vaya con Dios,
gt; Chuck, CABGx3
gt;
gt;
gt; quot;Ron Coderrequot; gt; wrote in message
gt; news
gt; gt; Actually, Chuck is on the right track!
gt; gt;
gt; gt; Try this:
gt; gt;
gt; gt; For a value in A1 (that may or may not have a colon):
gt; gt; B1:
gt; gt;
gt; =RIGHT(A1,LEN(A1)-LOOKUP(LEN(A1),FIND(quot;:quot;,quot;:quot;amp;A1,ROW(INDEX($A:$A,1,1 ):INDEX(
gt; $A:$A,LEN(A1),1)))) 1)
gt; gt;
gt; gt; The formula prepends a colon in the search section of the formula,
gt; gt; guaranteeing that a colon will be found.
gt; gt;
gt; gt; Does that help?
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;Louisquot; wrote:
gt; gt;
gt; gt; gt; that does work, yes. It adds several steps but I've made it work on
gt; this
gt; gt; gt; project today. The problem is I will need to do this everyday, and if
gt; there
gt; gt; gt; was a simple extension to the formula it would decrease the likelyhood
gt; of any
gt; gt; gt; errors. But this definitely works and I really appreciate the help.
gt; gt; gt;
gt; gt; gt; Thanks,
gt; gt; gt; --
gt; gt; gt; Louis
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;CLRquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; If Ron's formula works for you in every case except when there is no
gt; colon
gt; gt; gt; gt; in the string, then maybe you could just use a Helper column and
gt; CONCATENATE
gt; gt; gt; gt; a leading colon into every string.....it won't hurt the ones that
gt; already
gt; gt; gt; gt; have some and will allow the ones that don't to work.....
gt; gt; gt; gt;
gt; gt; gt; gt; Vaya con Dios,
gt; gt; gt; gt; Chuck, CABGx3
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Louisquot; gt; wrote in message
gt; gt; gt; gt; ...
gt; gt; gt; gt; gt; Very close. It actually works when there are categories before the
gt; item,
gt; gt; gt; gt; but
gt; gt; gt; gt; gt; for many items there is no category before it, for example, here is
gt; a
gt; gt; gt; gt; typical
gt; gt; gt; gt; gt; couple of rows:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; 1) AIR JETS- Pneumadyne:Air Jet Kit:AJK-HAN
gt; gt; gt; gt; gt; 2) AJB-1
gt; gt; gt; gt; gt; 3) CIRCUIT CONTROL VALVES- Pneumad:Pressure
gt; Regulators:11-Series:R11-RK-66
gt; gt; gt; gt; gt; 4) CIRCUIT CONTROL VALVES- Pneumad:Quick Exhaust:QE11-M-78
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; The items on the end are the part #'s I need. So the formula
gt; worked, I
gt; gt; gt; gt; just
gt; gt; gt; gt; gt; need something additional for the rows where there is no quot;:quot;.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Many thanks.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; Louis
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; quot;Ron Coderrequot; wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; This returns all of the text after the last occurrence of quot;:quot;
gt; gt; gt; gt; gt; gt; For a value in A1
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; B1:
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt;
gt; =RIGHT(A1,LEN(A1)-LOOKUP(LEN(A1),FIND(quot;:quot;,A1,ROW(INDEX($A:$A,1,1):IN DEX($A:$
gt; gt; gt; gt; A,LEN(A1),1)))))
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Does that help?
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; ***********
gt; gt; gt; gt; gt; gt; Regards,
gt; gt; gt; gt; gt; gt; Ron
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; XL2002, WinXP-Pro
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; quot;Louisquot; wrote:
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; Quickbooks exports our item list as such:
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; CIRCUIT CONTROL VALVES- Pneumad:Pressure
gt; gt; gt; gt; gt; gt; gt; Regulators:11-Series:Relieving:R11-RK-66
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; the quot;:quot; is the category the item to the right is in.
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; All I need from this is the part # at the end, the R11-RK-66.
gt; It will
gt; gt; gt; gt; gt; gt; gt; always be at the end of the string. the problem is there are
gt; 12K
gt; gt; gt; gt; parts, so I
gt; gt; gt; gt; gt; gt; gt; can't just quot;text to columnquot; and go that route, it would take
gt; forever.
gt; gt; gt; gt; I need
gt; gt; gt; gt; gt; gt; gt; a formula or macro I think to take out just the last item after
gt; the
gt; gt; gt; gt; last quot;:quot;
gt; gt; gt; gt; gt; gt; gt; A small kicker in this is some items may have 4 categories, some
gt; may
gt; gt; gt; gt; have 2,
gt; gt; gt; gt; gt; gt; gt; some may have 0.
gt; gt; gt; gt; gt; gt; gt; Thanks in advance for any ideas...
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; gt; gt; Louis
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt;
gt;
gt;
留言列表
