Extracting single piece of data

L

Louis

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...
 
L

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.
 
C

CLR

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
 
L

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,
 
R

Ron Coderre

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
 
C

CLR

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
 
R

Ron Coderre

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
 
Top