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
|