Extract values based on searchable reference

Basically I have all my data for each record contained in one cell, this
obviously is a no go. I need to pull data between two specific words:
"Address" & "City" (as an example), but not any of the other data within the
record. I've extracted all other data I needed already, I just can't seem to
find the appropriate formulas to get what I need done. Does anyone have any
suggestions? I'm very Excel literate, I've just never had any formal
training, so anything you throw at me I'll figure out! Just need a lil
guidence, thanx!

You need to post some examples of your data, so that we can see what
might be involved.

It would also help if you could describe what you have done already to
extract the other data (i.e. any formulae that you have used).

Pete

On Dec 9, 10:26*pm, D Reg D wrote:
Basically I have all my data for each record contained in one cell, this
obviously is a no go. I need to pull data between two specific words:
"Address" & "City" (as an example), but not any of the other data within the
record. I've extracted all other data I needed already, I just can't seem to
find the appropriate formulas to get what I need done. Does anyone have any
suggestions? I'm very Excel literate, I've just never had any formal
training, so anything you throw at me I'll figure out! Just need a lil
guidence, thanx!

I agree 101% with what PeteUK asked for/said. But consider this possible
starting point:
In A1 is the phrase:
ralph's address should be here before the city name
and this formula would extract the phrase ' should be here before the ' from
it:
=MID(A1,FIND("address",A1)+LEN("address"),FIND("ci ty",A1,FIND("address",A1))-FIND("address",A1)+LEN("address"))

Obviously that's not very robust, since "address" and "city" are hard coded
into the formula and I doubt that you're just looking to find a single
address and/or city in your list.

"D Reg" wrote:

Basically I have all my data for each record contained in one cell, this
obviously is a no go. I need to pull data between two specific words:
"Address" & "City" (as an example), but not any of the other data within the
record. I've extracted all other data I needed already, I just can't seem to
find the appropriate formulas to get what I need done. Does anyone have any
suggestions? I'm very Excel literate, I've just never had any formal
training, so anything you throw at me I'll figure out! Just need a lil
guidence, thanx!

Oops, I missed a needed parenthesis, formula works better like this:
=MID(A1,FIND("address",A1)+LEN("address"),FIND("ci ty",A1,FIND("address",A1))-(FIND("address",A1)+LEN("address")))


"JLatham" wrote:

I agree 101% with what PeteUK asked for/said. But consider this possible
starting point:
In A1 is the phrase:
ralph's address should be here before the city name
and this formula would extract the phrase ' should be here before the ' from
it:
=MID(A1,FIND("address",A1)+LEN("address"),FIND("ci ty",A1,FIND("address",A1))-FIND("address",A1)+LEN("address"))

Obviously that's not very robust, since "address" and "city" are hard coded
into the formula and I doubt that you're just looking to find a single
address and/or city in your list.

"D Reg" wrote:

Basically I have all my data for each record contained in one cell, this
obviously is a no go. I need to pull data between two specific words:
"Address" & "City" (as an example), but not any of the other data within the
record. I've extracted all other data I needed already, I just can't seem to
find the appropriate formulas to get what I need done. Does anyone have any
suggestions? I'm very Excel literate, I've just never had any formal
training, so anything you throw at me I'll figure out! Just need a lil
guidence, thanx!

On Tue, 9 Dec 2008 14:26:00 -0800, D Reg D
wrote:

Basically I have all my data for each record contained in one cell, this
obviously is a no go. I need to pull data between two specific words:
"Address" & "City" (as an example), but not any of the other data within the
record. I've extracted all other data I needed already, I just can't seem to
find the appropriate formulas to get what I need done. Does anyone have any
suggestions? I'm very Excel literate, I've just never had any formal
training, so anything you throw at me I'll figure out! Just need a lil
guidence, thanx!

It should be easily doable with regular expressions, but you'll need to provide
more information.

For example, with this UDF:

==============================
Option Explicit
Function RegexSub(Str As String, SrchFor As String, ReplWith As String) _
As String
Dim objRegExp As Object
Set objRegExp = CreateObject("vbscript.regexp")
objRegExp.Pattern = SrchFor
objRegExp.IgnoreCase = True
objRegExp.Global = True
objRegExp.MultiLine = True

RegexSub = objRegExp.Replace(Str, ReplWith)

End Function
===============================

this formula would return everything between Address and City (except for the
spaces after Address and Before City:

=RegexSub(A1,"^[\s\S]*Address\s+([\s\S]+)\s+City[\s\S]+$","$1")

And the SrchFor string could be easily modified to return other segments of
your data.
--ron

On Tue, 09 Dec 2008 20:27:04 -0500, Ron Rosenfeld
wrote:

On Tue, 9 Dec 2008 14:26:00 -0800, D Reg D
wrote:

Basically I have all my data for each record contained in one cell, this
obviously is a no go. I need to pull data between two specific words:
"Address" & "City" (as an example), but not any of the other data within the
record. I've extracted all other data I needed already, I just can't seem to
find the appropriate formulas to get what I need done. Does anyone have any
suggestions? I'm very Excel literate, I've just never had any formal
training, so anything you throw at me I'll figure out! Just need a lil
guidence, thanx!

It should be easily doable with regular expressions, but you'll need to provide
more information.

For example, with this UDF:

==============================
Option Explicit
Function RegexSub(Str As String, SrchFor As String, ReplWith As String) _
As String
Dim objRegExp As Object
Set objRegExp = CreateObject("vbscript.regexp")
objRegExp.Pattern = SrchFor
objRegExp.IgnoreCase = True
objRegExp.Global = True
objRegExp.MultiLine = True

RegexSub = objRegExp.Replace(Str, ReplWith)

End Function
===============================

this formula would return everything between Address and City (except for the
spaces after Address and Before City:

=RegexSub(A1,"^[\s\S]*Address\s+([\s\S]+)\s+City[\s\S]+$","$1")

And the SrchFor string could be easily modified to return other segments of
your data.
--ron

Of course, there would be an issue with parsing out an address if the street
name included the word "City"; and there are numerous such in the US. (e.g.
Frio City Rd, San Antonio, TX)


--ron