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