Calculated fields to split data from one field to two

G

Guest

Hello-

I am trying to take data from field [Market] and create two fields called
[PLAN_TYPE_NM] and [MARKET_NM].
The data is text and looks like this:
Market
PFFS IOWA
PFFS CAS IOWA
PFFS IOWA
PFFS IOWA

What I'm trying to do is make MARKET_NM Iowa, and everything else
PLAN_TYPE_NM. I've got IOWA separated by using,
MARKET_NM: Right([Market],InStr([Market]," ")-1)

My problem is I can't seem to get PFFS CAS into it's own field. What screws
me up is the fact the field could contain either PFFS, or PFFS CAS. I have
looked at multiple posts and everything I've tried using Left() and Mid()
doesn't seem to give me what I'm looking for.

Any help would be greatly appreciated as I'm sure I'm missing something
simple.
Thanks,

Jay
 
G

Guest

Instead of the Right function try the Replace to replace "IOWA" with nothing
to remove it

Replace([Market],"IOWA","")

That incase you always want to remove "IOWA"

*****
If that not the case and there are different names and not just "IOWA" try
using InStrRev to get the space before the last string

Right([Market],InStrRev([Market]," ")-1)

*****
Or, In the InStr look for " IOWA" with space

Right([Market],InStrRev([Market]," IOWA")-1)
 
G

Guest

Instead of the Right function try the Replace to replace "IOWA" with nothing
to remove it

Replace([Market],"IOWA","")

That incase you always want to remove "IOWA"

*****
If that not the case and there are different names and not just "IOWA" try
using InStrRev to get the space before the last string

Right([Market],InStrRev([Market]," ")-1)

*****
Or, In the InStr look for " IOWA" with space

Right([Market],InStr([Market]," IOWA")-1)
 
J

John Spencer

Try the following. If your version of Access does not support InStrRev you
will get an error message

Trim(Left([Market],InStrRev(" " & [Market]," ")))


By the way I don't see how your expression avoid returning CAS IOWA from
PFFS CAS IOWA
You might try the following to get just the market
Trim(Mid([Market],InStrRev(" " &[Market]," ")))

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
G

Guest

Instead of the Right function try the Replace to replace "IOWA" with nothing
to remove it

Replace([Market],"IOWA","")
Click to expand...

This worked perfectly! The state name will change, but I can just replace
"IOWA" with "Insert State Name Here".
I had a feeling I was making it more difficult than it had to be.

Thanks again for your help, and very quick response!

-Jay

Ofer Cohen said:
Instead of the Right function try the Replace to replace "IOWA" with nothing
to remove it

Replace([Market],"IOWA","")

That incase you always want to remove "IOWA"

*****
If that not the case and there are different names and not just "IOWA" try
using InStrRev to get the space before the last string

Right([Market],InStrRev([Market]," ")-1)

*****
Or, In the InStr look for " IOWA" with space

Right([Market],InStrRev([Market]," IOWA")-1)

--
Good Luck
BS"D


Jay said:
Hello-

I am trying to take data from field [Market] and create two fields called
[PLAN_TYPE_NM] and [MARKET_NM].
The data is text and looks like this:
Market
PFFS IOWA
PFFS CAS IOWA
PFFS IOWA
PFFS IOWA

What I'm trying to do is make MARKET_NM Iowa, and everything else
PLAN_TYPE_NM. I've got IOWA separated by using,
MARKET_NM: Right([Market],InStr([Market]," ")-1)

My problem is I can't seem to get PFFS CAS into it's own field. What screws
me up is the fact the field could contain either PFFS, or PFFS CAS. I have
looked at multiple posts and everything I've tried using Left() and Mid()
doesn't seem to give me what I'm looking for.

Any help would be greatly appreciated as I'm sure I'm missing something
simple.
Thanks,

Jay
Click to expand...
Click to expand...
 
G

Guest

By the way I don't see how your expression avoid returning CAS IOWA from
PFFS CAS IOWA
Click to expand...

I used Right([Market],InStr([Market]," ")-1) and it returned only "IOWA"
from either PFFS CAS IOWA, or PFFS IOWA.
You might try the following to get just the market
Trim(Mid([Market],InStrRev(" " &[Market]," ")))
Click to expand...

This worked as well, is there a right or wrong here? Or just two different
ways to get at the same data?

Thanks again for assistance,

Jay


John Spencer said:
Try the following. If your version of Access does not support InStrRev you
will get an error message

Trim(Left([Market],InStrRev(" " & [Market]," ")))


By the way I don't see how your expression avoid returning CAS IOWA from
PFFS CAS IOWA
You might try the following to get just the market
Trim(Mid([Market],InStrRev(" " &[Market]," ")))

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Jay said:
Hello-

I am trying to take data from field [Market] and create two fields called
[PLAN_TYPE_NM] and [MARKET_NM].
The data is text and looks like this:
Market
PFFS IOWA
PFFS CAS IOWA
PFFS IOWA
PFFS IOWA

What I'm trying to do is make MARKET_NM Iowa, and everything else
PLAN_TYPE_NM. I've got IOWA separated by using,
MARKET_NM: Right([Market],InStr([Market]," ")-1)

My problem is I can't seem to get PFFS CAS into it's own field. What
screws
me up is the fact the field could contain either PFFS, or PFFS CAS. I
have
looked at multiple posts and everything I've tried using Left() and Mid()
doesn't seem to give me what I'm looking for.

Any help would be greatly appreciated as I'm sure I'm missing something
simple.
Thanks,

Jay
Click to expand...
Click to expand...
 
J

John Spencer

Coincidence.
Your INSTR returns 5 - the position of the first space in PFSS CAS IOWA and
then you get the 5 right most characters

Try it with
PFFS CAS NEBRASKA
and you will get RASKA returned.

InstrRev returns the position of the last space.

So your method will give you incorrect results.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Jay said:
By the way I don't see how your expression avoid returning CAS IOWA from
PFFS CAS IOWA
Click to expand...

I used Right([Market],InStr([Market]," ")-1) and it returned only "IOWA"
from either PFFS CAS IOWA, or PFFS IOWA.
You might try the following to get just the market
Trim(Mid([Market],InStrRev(" " &[Market]," ")))
Click to expand...

This worked as well, is there a right or wrong here? Or just two
different
ways to get at the same data?

Thanks again for assistance,

Jay


John Spencer said:
Try the following. If your version of Access does not support InStrRev
you
will get an error message

Trim(Left([Market],InStrRev(" " & [Market]," ")))


By the way I don't see how your expression avoid returning CAS IOWA from
PFFS CAS IOWA
You might try the following to get just the market
Trim(Mid([Market],InStrRev(" " &[Market]," ")))

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Jay said:
Hello-

I am trying to take data from field [Market] and create two fields
called
[PLAN_TYPE_NM] and [MARKET_NM].
The data is text and looks like this:
Market
PFFS IOWA
PFFS CAS IOWA
PFFS IOWA
PFFS IOWA

What I'm trying to do is make MARKET_NM Iowa, and everything else
PLAN_TYPE_NM. I've got IOWA separated by using,
MARKET_NM: Right([Market],InStr([Market]," ")-1)

My problem is I can't seem to get PFFS CAS into it's own field. What
screws
me up is the fact the field could contain either PFFS, or PFFS CAS. I
have
looked at multiple posts and everything I've tried using Left() and
Mid()
doesn't seem to give me what I'm looking for.

Any help would be greatly appreciated as I'm sure I'm missing something
simple.
Thanks,

Jay
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Coincidence.
Your INSTR returns 5 - the position of the first space in PFSS CAS IOWA and
then you get the 5 right most characters

Try it with
PFFS CAS NEBRASKA
and you will get RASKA returned.

InstrRev returns the position of the last space.

So your method will give you incorrect results.
Click to expand...

Absolutely right! Thank you for catching that and saving me a headache down
the road.
Thanks again for the knowledge.

Jay

John Spencer said:
Coincidence.
Your INSTR returns 5 - the position of the first space in PFSS CAS IOWA and
then you get the 5 right most characters

Try it with
PFFS CAS NEBRASKA
and you will get RASKA returned.

InstrRev returns the position of the last space.

So your method will give you incorrect results.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Jay said:
By the way I don't see how your expression avoid returning CAS IOWA from
PFFS CAS IOWA
Click to expand...

I used Right([Market],InStr([Market]," ")-1) and it returned only "IOWA"
from either PFFS CAS IOWA, or PFFS IOWA.
You might try the following to get just the market
Trim(Mid([Market],InStrRev(" " &[Market]," ")))
Click to expand...

This worked as well, is there a right or wrong here? Or just two
different
ways to get at the same data?

Thanks again for assistance,

Jay


John Spencer said:
Try the following. If your version of Access does not support InStrRev
you
will get an error message

Trim(Left([Market],InStrRev(" " & [Market]," ")))


By the way I don't see how your expression avoid returning CAS IOWA from
PFFS CAS IOWA
You might try the following to get just the market
Trim(Mid([Market],InStrRev(" " &[Market]," ")))

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hello-

I am trying to take data from field [Market] and create two fields
called
[PLAN_TYPE_NM] and [MARKET_NM].
The data is text and looks like this:
Market
PFFS IOWA
PFFS CAS IOWA
PFFS IOWA
PFFS IOWA

What I'm trying to do is make MARKET_NM Iowa, and everything else
PLAN_TYPE_NM. I've got IOWA separated by using,
MARKET_NM: Right([Market],InStr([Market]," ")-1)

My problem is I can't seem to get PFFS CAS into it's own field. What
screws
me up is the fact the field could contain either PFFS, or PFFS CAS. I
have
looked at multiple posts and everything I've tried using Left() and
Mid()
doesn't seem to give me what I'm looking for.

Any help would be greatly appreciated as I'm sure I'm missing something
simple.
Thanks,

Jay
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Split data in field on character return 0
Find fields with only numbers in Text formatted column 2
split a field 3
Complicated Calculated Field Logic 0
two null fields 4
split up one field into multiple fields 2
Splitting a Field Question 2
Split two data elements that are in one field 4

Top