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

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

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

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

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

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

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

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