Hi,

I remember reading a code for splitting names and email addresses from a
*.txt file using (via importing) excel. But forget the content...

The source *.txt file is in the format such as:
----------------------------------------
marry chris
adam kenny
....
...
etc

I need to split the two info into two adjacent columns such as:
column 1 having the name & surname info ONLY
column 2 having the email address info ONLY
-----------------
Hope experts could remind me the way.
Sincerely

Thanks Norman,
I'll give it a try and turn back to here...

"Norman Jones" wrote in message
...
Hi Cheker:

Assume your names and email addresses start in A1, try:

Sub Tester2()
Dim rng As Range, rCell As Range
Dim pos1 As Long, pos2 As Long
Dim sStr As String
Dim sh As Worksheet
Set sh = Sheets("Sheet1") '<<======== CHANGE

With sh
Set rng = Range(.Range("A1"), _
.Cells(Rows.Count, "A").End(xlUp))
End With

For Each rCell In rng
pos1 = InStr(rCell.Value, " ")
pos2 = InStrRev(rCell.Value, " ")
rCell(1, 2).Value = Left(rCell.Value, pos1 - 1)
sStr = Mid(rCell.Value, pos2 + 1)
ActiveSheet.Hyperlinks.Add _
Anchor:=rCell(1, 3), _
Address:="mailto:" & sStr, TextToDisplay:=sStr
Next
rng.Delete shift:=xlToLeft
End Sub


---
Regards,
Norman



"Cheker" wrote in message
...
Hi,

I remember reading a code for splitting names and email addresses from a
*.txt file using (via importing) excel. But forget the content...

The source *.txt file is in the format such as:
----------------------------------------
marry chris
adam kenny
...
..
etc

I need to split the two info into two adjacent columns such as:
column 1 having the name & surname info ONLY
column 2 having the email address info ONLY
-----------------
Hope experts could remind me the way.
Sincerely

Hi Norman,
Probably there is something wrong with the coding.
It is cutting and pasting names (without surnames) to column A, while
putting just a live (?) "mailto:" text to column B without any email
address info.
p.s.: I copy pasted the whole text list to column A beforehand...
Sincerely

"Norman Jones" wrote in message
...
Hi Cheker:

Assume your names and email addresses start in A1, try:

Sub Tester2()
Dim rng As Range, rCell As Range
Dim pos1 As Long, pos2 As Long
Dim sStr As String
Dim sh As Worksheet
Set sh = Sheets("Sheet1") '<<======== CHANGE

With sh
Set rng = Range(.Range("A1"), _
.Cells(Rows.Count, "A").End(xlUp))
End With

For Each rCell In rng
pos1 = InStr(rCell.Value, " ")
pos2 = InStrRev(rCell.Value, " ")
rCell(1, 2).Value = Left(rCell.Value, pos1 - 1)
sStr = Mid(rCell.Value, pos2 + 1)
ActiveSheet.Hyperlinks.Add _
Anchor:=rCell(1, 3), _
Address:="mailto:" & sStr, TextToDisplay:=sStr
Next
rng.Delete shift:=xlToLeft
End Sub


---
Regards,
Norman



"Cheker" wrote in message
...
Hi,

I remember reading a code for splitting names and email addresses from a
*.txt file using (via importing) excel. But forget the content...

The source *.txt file is in the format such as:
----------------------------------------
marry chris
adam kenny
...
..
etc

I need to split the two info into two adjacent columns such as:
column 1 having the name & surname info ONLY
column 2 having the email address info ONLY
-----------------
Hope experts could remind me the way.
Sincerely

Hi Norman,
I know that I am taking too much of your time but this new version seems to
scan the whole list (there is a fast flicker on the screen when the macro is
executed), but finishes the macro with the "last" item display splitted on
the worksheet.
TIA
Checker

"Norman Jones" wrote in message
...
Hi Checker,

"Cheker" wrote:
Probably there is something wrong with the coding.

Sorry - I failed to allow for both name and surname.

Try :
Sub Tester2()
Dim rng As Range, rCell As Range
Dim pos1 As Long, pos2 As Long, pos3 As Long
Dim sStr As String
Dim sh As Worksheet
Set sh = Sheets("Sheet1") '<<======== CHANGE

With sh
Set rng = Range(.Range("A1"), _
.Cells(Rows.Count, "A").End(xlUp))
End With

For Each rCell In rng
pos1 = InStr(rCell.Value, " ")
pos2 = InStrRev(rCell.Value, " ")
rCell(1, 2).Value = Left(rCell.Value, pos2 - 1)
sStr = Mid(rCell.Value, pos2 + 1)
ActiveSheet.Hyperlinks.Add _
Anchor:=rCell(1, 3), _
Address:=sStr, TextToDisplay:=sStr
Next
rng.Delete shift:=xlToLeft
End Sub


---
Regards,
Norman



"Cheker" wrote:
Hi Norman,
Probably there is something wrong with the coding.

Hi Norman,
Maybe the code is confused by some characters included in column A data.
Because when I reduced the size and delete records including characters that
are not present in Latin alphabet the code split "some" of the data
correctly. Thus I think I should concentrate more on the list. Thanks for
answering my questions step by step.
Regards



"Norman Jones" wrote in message
...
Hi Cheker,

there is a fast flicker on the screen when the macro is executed

At the top of the macro, after the last Dim statement, insert the new
line:

Application.ScreenUpdating = False

At the foot of the macro, before the End Sub line, insert the new line:

Application.ScreenUpdating = True

This should have the incidental advantage of improving speed of
operation.


finishes the macro with the "last" item display splitted on the
worksheet.

The selection is not changed by the procedure but I am not sure what you
mean. Perhaps you could explain in greater detail.

In my tests, the procedure, as requested, splits all data in column A,
resulting in name and surnames in column A and email addresses in column
B.

Hi Norman,
I think You are right about the data format. It is a long list and some have
more then two spaces...I cleaned a relatively small part of it and try to
run the macro for that list. Worked OK.
Thanks a lot.


"Norman Jones" wrote in message
...
Hi Cheker,

The split is effected by establishing the the first, second and last
spaces
in the column A strings, so I doubt that the use of specific characters
has
a bearing on your problem.

Is it possible that some of your data does not correspond to your original
example specification:

name / single space / name / single space / email address ?

In any event, perhaps it would be useful to give examples of problematic
strings.


---
Regards,
Norman



"Cheker" wrote in message
...
Hi Norman,
Maybe the code is confused by some characters included in column A data.
Because when I reduced the size and delete records including characters
that
are not present in Latin alphabet the code split "some" of the data
correctly. Thus I think I should concentrate more on the list. Thanks
for
answering my questions step by step.
Regards



"Norman Jones" wrote in message
...
Hi Cheker,

there is a fast flicker on the screen when the macro is executed

At the top of the macro, after the last Dim statement, insert the new
line:

Application.ScreenUpdating = False

At the foot of the macro, before the End Sub line, insert the new line:

Application.ScreenUpdating = True

This should have the incidental advantage of improving speed of
operation.


finishes the macro with the "last" item display splitted on the
worksheet.

The selection is not changed by the procedure but I am not sure what
you
mean. Perhaps you could explain in greater detail.

In my tests, the procedure, as requested, splits all data in column A,
resulting in name and surnames in column A and email addresses in
column
B.
According to whether you require column B data hyperlinked or not, use
the
last or the penultimate versions.

I know that I am taking too much of your time ...

Do not worry on that account. The primary consideration is to obtain
the
functionality you seek

---
Regards,
Norman



"Cheker" wrote in message
...
Hi Norman,
I know that I am taking too much of your time but this new version
seems
to
scan the whole list (there is a fast flicker on the screen when the
macro
is
executed), but finishes the macro with the "last" item display
splitted
on
the worksheet.
TIA
Checker

"Norman Jones" wrote in message
...
Hi Checker,

"Cheker" wrote:
Probably there is something wrong with the coding.

Sorry - I failed to allow for both name and surname.

Try :
Sub Tester2()
Dim rng As Range, rCell As Range
Dim pos1 As Long, pos2 As Long, pos3 As Long
Dim sStr As String
Dim sh As Worksheet
Set sh = Sheets("Sheet1") '<<======== CHANGE

With sh
Set rng = Range(.Range("A1"), _
.Cells(Rows.Count, "A").End(xlUp))
End With

For Each rCell In rng
pos1 = InStr(rCell.Value, " ")
pos2 = InStrRev(rCell.Value, " ")
rCell(1, 2).Value = Left(rCell.Value, pos2 - 1)
sStr = Mid(rCell.Value, pos2 + 1)
ActiveSheet.Hyperlinks.Add _
Anchor:=rCell(1, 3), _
Address:=sStr, TextToDisplay:=sStr
Next
rng.Delete shift:=xlToLeft
End Sub


---
Regards,
Norman



"Cheker" wrote:
Hi Norman,
Probably there is something wrong with the coding.