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

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):INDEX($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

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" <Louis@discussions.microsoft.com> wrote in message
news:A4652427-5E80-4D20-B91B-1E711909A4AD@microsoft.com...
> 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):INDEX($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

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" <Louis@discussions.microsoft.com> wrote in message
> news:A4652427-5E80-4D20-B91B-1E711909A4AD@microsoft.com...
> > 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):INDEX($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
>
>
>

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" <Louis@discussions.microsoft.com> wrote in message
> > news:A4652427-5E80-4D20-B91B-1E711909A4AD@microsoft.com...
> > > 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):INDEX($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
> >
> >
> >

That is UNBELIEVABLE Ron........I stand in awe!.....

Vaya con Dios,
Chuck, CABGx3


"Ron Coderre" <ronSKIPTHIScoderre@bigfoot.com> wrote in message
news:D8DE5900-2D42-42F9-BEB1-E9B89E1799CB@microsoft.com...
> 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" <Louis@discussions.microsoft.com> wrote in message
> > > news:A4652427-5E80-4D20-B91B-1E711909A4AD@microsoft.com...
> > > > 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):INDEX($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
> > >
> > >
> > >

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" <Louis@discussions.microsoft.com> wrote in message
news:29F93DC6-53C0-4084-92D0-821DC7FDAD7C@microsoft.com...
> 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" <Louis@discussions.microsoft.com> wrote in message
> > news:A4652427-5E80-4D20-B91B-1E711909A4AD@microsoft.com...
> > > 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):INDEX($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
> >
> >
> >

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" <ronSKIPTHIScoderre@bigfoot.com> wrote in message
> news:D8DE5900-2D42-42F9-BEB1-E9B89E1799CB@microsoft.com...
> > 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" <Louis@discussions.microsoft.com> wrote in message
> > > > news:A4652427-5E80-4D20-B91B-1E711909A4AD@microsoft.com...
> > > > > 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):INDEX($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
> > > >
> > > >
> > > >
>
>
>