Parsing data into multiple fields

  • Thread starter Thread starter Avid Reader
  • Start date Start date
A

Avid Reader

Hi all, I'm looking to be able to parse data into multiple fields without
having to do it manually.

The data below is in the exact current format. What I need to be able to do
is to have it parse on the backslashes(\). It's currently being used by a
Foxpro program that uses the (\) to do a carriage return so that it prints
on our packing label as such:

IMX PG
4(24),5G
CS +1" 2U 1/32




IMX PG\4(24),5G\CS +1" 2U 1/32

Thanks for any assistance.
 
Take a look at the Split function or you can use Instr, Right, Left, Mid to
accomplish this. That might look something like the following

Field1: Left(YourField,InStr(1,YourField,"\")-1)

Field2:
Mid(Mid(YourField,Instr(1,YourField,"\")+1),Instr(1,Mid(YourField,Instr(1,YourField,"\")+1),"\")+1)

Field3: Right(YourField,InstrRev(YourField,"\")+1)
 
The left function works fine but the Right and Mid functions not so good.

The Mid function returns #Error and the Right function is hit and miss.
Sometimes it pulls all the way back to the "\" and sometimes it includes the
backslash.

Any other ideas on the correct formula. I think what John has given me is
real close but not quite. Thanks everyone.
 
Whoops. I guess I must have been tired or had too much wine or been distracted
by grandkids or ... (choose your excuse).

Try the following for field3 - note the replacement of MID for Right

Field3: MID(YourField,InstrRev(YourField,"\")+1)

And it looks as if I've dropped some parens in the FIELD2 calculation AND used
the wrong calculation. Play with it and see if you can adjust it to yield
correct results. The idea is to strip off the first part upto and including the
first slash and then strip off the part after the last slash.

So try doing this in chunks.

Mid(YourField,Instr(1,YourField,"\")-1) should give you all the information
after the first slash.

LEFT(X,Instr(1,X,"\")-1) Where X is the above calculation should find the part
before the next slash. So the formula for part 2 becomes

LEFT(Mid(YourField,Instr(1,YourField,"\")-1),Instr(1,Mid(YourField,Instr(1,YourField,"\")-1),"\")-1)

At least I HOPE that's right. If not, make sure that the right and left parens
match up (in count) and then try playing with the formula to get what you want.
 
Thanks John it worked great. Here are the correct formulas that I used. I
hope this helps someone else out there.

Left: Left([MyField],InStr([MyField],"\")-1)
Mid:
Left(Mid([MyField],InStr(1,[MyField],"\")+1),InStr(1,Mid([MyField],InStr(1,[
MyField],"\")+1),"\")-1)
Right: Mid([MyField],InStrRev([MyField],"\")+1)
 
Back
Top