Extract values based on searchable reference

  • Thread starter Thread starter D Reg
  • Start date Start date
D

D Reg

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
 
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("city",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.
 
Oops, I missed a needed parenthesis, formula works better like this:
=MID(A1,FIND("address",A1)+LEN("address"),FIND("city",A1,FIND("address",A1))-(FIND("address",A1)+LEN("address")))
 
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! :)
Click to expand...

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
 
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! :)
Click to expand...

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
Click to expand...

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
 
Back
Top