Creating new column, with data cleaned of prefixes and suffixes

J

JeffL

I have a table that has a lot of library call numbers with forms like:

789.78
q123.456 A
123.4567 B123
f678.4 Z
VIDEO 398.6 A
J VIDEO 888 L12
Mu q780.89 A
J Mu q789.1 A1

And a list of prefixes to remove, i.e, "q", "f", "Mu q", "J Mu q"

I need a new column that removes prefixes from the list BEFORE the first
number, and removes everything AFTER the first number, so end up with this:

789.78
123.456
123.4567
678.4
VIDEO 398.6
J VIDEO 888
780.89
789.1

How would I do that? I was looking for a find function in the expression
builder to work with replace or mid, and Find and replace in the table won't
work because hundreds of thousands of records need to be changed, and it will
only do 9000 or so before stopping with an error.

TIA

Jeff Lima
 
K

KARL DEWEY

Try this --
New_Field: IIf(InStrRev(Replace(Replace(Replace(Replace([Call_Number],"J Mu
q",""),"f",""),"Mu q",""),"q",""),"
")<1,Replace(Replace(Replace(Replace([Call_Number],"J Mu q",""),"f",""),"Mu
q",""),"q",""),Left(Replace(Replace(Replace(Replace([Call_Number],"J Mu
q",""),"f",""),"Mu
q",""),"q",""),InStrRev(Replace(Replace(Replace(Replace([Call_Number],"J Mu
q",""),"f",""),"Mu q",""),"q","")," ")-1))
 
J

JeffL

This seems to work with the data examples I gave, but I left out cases where
more than one suffix follows the numbered part. In a case like 333.3 O16 A,
I want it to return 333.3. Right now it returns 333.16 O16.

Thanks for your help.

Jeff Lima

KARL DEWEY said:
Try this --
New_Field: IIf(InStrRev(Replace(Replace(Replace(Replace([Call_Number],"J Mu
q",""),"f",""),"Mu q",""),"q",""),"
")<1,Replace(Replace(Replace(Replace([Call_Number],"J Mu q",""),"f",""),"Mu
q",""),"q",""),Left(Replace(Replace(Replace(Replace([Call_Number],"J Mu
q",""),"f",""),"Mu
q",""),"q",""),InStrRev(Replace(Replace(Replace(Replace([Call_Number],"J Mu
q",""),"f",""),"Mu q",""),"q","")," ")-1))

--
Build a little, test a little.


JeffL said:
I have a table that has a lot of library call numbers with forms like:

789.78
q123.456 A
123.4567 B123
f678.4 Z
VIDEO 398.6 A
J VIDEO 888 L12
Mu q780.89 A
J Mu q789.1 A1

And a list of prefixes to remove, i.e, "q", "f", "Mu q", "J Mu q"

I need a new column that removes prefixes from the list BEFORE the first
number, and removes everything AFTER the first number, so end up with this:

789.78
123.456
123.4567
678.4
VIDEO 398.6
J VIDEO 888
780.89
789.1

How would I do that? I was looking for a find function in the expression
builder to work with replace or mid, and Find and replace in the table won't
work because hundreds of thousands of records need to be changed, and it will
only do 9000 or so before stopping with an error.

TIA

Jeff Lima
Click to expand...
Click to expand...
 
K

KARL DEWEY

Maybe this --
New_Field: IIf(Val(Replace(Replace(Replace(Replace([Call_Number],"J Mu
q",""),"f",""),"Mu
q",""),"q",""))<>0,Val(Replace(Replace(Replace(Replace([Call_Number],"J Mu
q",""),"f",""),"Mu
q",""),"q","")),IIf(InStrRev(Replace(Replace(Replace(Replace([Call_Number],"J
Mu q",""),"f",""),"Mu q",""),"q",""),"
")<1,Replace(Replace(Replace(Replace([Call_Number],"J Mu q",""),"f",""),"Mu
q",""),"q",""),Left(Replace(Replace(Replace(Replace([Call_Number],"J Mu
q",""),"f",""),"Mu
q",""),"q",""),InStrRev(Replace(Replace(Replace(Replace([Call_Number],"J Mu
q",""),"f",""),"Mu q",""),"q","")," ")-1)))
--
Build a little, test a little.


JeffL said:
This seems to work with the data examples I gave, but I left out cases where
more than one suffix follows the numbered part. In a case like 333.3 O16 A,
I want it to return 333.3. Right now it returns 333.16 O16.

Thanks for your help.

Jeff Lima

KARL DEWEY said:
Try this --
New_Field: IIf(InStrRev(Replace(Replace(Replace(Replace([Call_Number],"J Mu
q",""),"f",""),"Mu q",""),"q",""),"
")<1,Replace(Replace(Replace(Replace([Call_Number],"J Mu q",""),"f",""),"Mu
q",""),"q",""),Left(Replace(Replace(Replace(Replace([Call_Number],"J Mu
q",""),"f",""),"Mu
q",""),"q",""),InStrRev(Replace(Replace(Replace(Replace([Call_Number],"J Mu
q",""),"f",""),"Mu q",""),"q","")," ")-1))

--
Build a little, test a little.


JeffL said:
I have a table that has a lot of library call numbers with forms like:

789.78
q123.456 A
123.4567 B123
f678.4 Z
VIDEO 398.6 A
J VIDEO 888 L12
Mu q780.89 A
J Mu q789.1 A1

And a list of prefixes to remove, i.e, "q", "f", "Mu q", "J Mu q"

I need a new column that removes prefixes from the list BEFORE the first
number, and removes everything AFTER the first number, so end up with this:

789.78
123.456
123.4567
678.4
VIDEO 398.6
J VIDEO 888
780.89
789.1

How would I do that? I was looking for a find function in the expression
builder to work with replace or mid, and Find and replace in the table won't
work because hundreds of thousands of records need to be changed, and it will
only do 9000 or so before stopping with an error.

TIA

Jeff Lima
Click to expand...
Click to expand...
Click to expand...
 
Top