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