Isolating certain text

R

Robert

Hi,
I have a large document, and it has email addresses, addresses, ect in it,
and I want to be able to filter all the information out of it but the email
addresses, how can I do this in Excel or Word?
 
C

Chris Premo

Robert said:
Hi,
I have a large document, and it has email addresses, addresses, ect
in it, and I want to be able to filter all the information out of it
but the email addresses, how can I do this in Excel or Word?
Click to expand...


By "filter all the information out of it" do you mean export the data??

If you mean export it out, that will depend on how the data is
arranged/stated in the source document. I do this sort of extraction
using excel from a Txt file that I generate from my Novell servers.

Can you post some “generic†examples of how the document is
arranged? That will give us a better idea as to how to proceed.


--
 
R

Robert

The document has media listed in it in the following types of format,

Name
Address
Telephone
Fax
Name
Email
Email
Information

and I want to be able to export only the email addresses from this large
file.....
 
S

Shane Devenshire

Hi,

Is this an Excel file? Do you want to export both email addresses? Is this
data laid out as fields from left to right or is it all in one column? If
vertically, are all data sets exactly the same structure - that is 8 rows of
data with two rows for email addresses? Does the next entry follow the prior
entry directly or is there a space between each? By entry I mean the info
for each person.
 
C

Chris Premo

Robert said:
The document has media listed in it in the following types of format,

Name
Address
Telephone
Fax
Name
Email
Email
Information

and I want to be able to export only the email addresses from this
large file.....
Click to expand...


Workbooks.OpenText Filename:=strPath & "\Config.txt", Origin:=437, _
StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False,
Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=True



Cells.Select
Range("A1").Activate
ActiveWorkbook.Worksheets("Config").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Config").Sort.SortFields.Add
Key:=Range("B1"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("Config").Sort
.SetRange Range("A1:X10000")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With



TextStr = "Email"
Call FindText
ActRow = ActiveCell.Row - 1
Rows("1:" & ActRow).Select
Selection.Delete Shift:=xlUp

Range("A1").Select



Fax


Sub FindText()

Selection.Find(What:=TextStr, After:=ActiveCell, LookIn:=xlFormulas
_
, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

End Sub



--
 
C

Chris Premo

I sent the other answer before I was finished. Here are some sample
code that I use to do this type of thing. There are three functions
and then some basic code to help you sort and delete the un-needed data
prior to "Save As" function. This should get you going.

Let me know if this helps.


*******************************************************************

Function OpenConfigFile()

Workbooks.OpenText Filename:=strPath & "\YOUR FILE NAME.txt",
Origin:=437, _
StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False,
Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=True

End Function



Function FindText()

Selection.Find(What:=TextStr, After:=ActiveCell, LookIn:=xlFormulas
_
, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

End Function


Function SaveConfigFile()

Application.DisplayAlerts = False
ChDir strPath
ActiveWorkbook.SaveAs Filename:=strPath & "\" & TextStr2,
FileFormat:= _
xlText, CreateBackup:=False
ActiveWorkbook.Close
Application.DisplayAlerts = True

End Function




Cells.Select
Range("A1").Activate
ActiveWorkbook.Worksheets("Config").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Config").Sort.SortFields.Add
Key:=Range("A1"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("Config").Sort
.SetRange Range("A1:X10000")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Columns("A:A").Select
TextStr = "Email"
Call FindText
ActRow = ActiveCell.Row – 1

Rows("1:" & ActRow).Select
Range("A" & ActRow).Activate
Selection.Delete Shift:=xlUp

Columns("A:A").Select
TextStr = "FAX"
Call FindText
ActRow = ActiveCell.Row

Rows(ActRow & “:10000â€).Select
Range("A" & ActRow).Activate
Selection.Delete Shift:=xlUp


TextStr2 = "Email Addresses.TXT"
Call SaveConfigFile

--
 
Top