modifying a excel *.csv file problem

[J_J]

I have a *.csv address file in excel page format. All the records resides in
column A:A and each line consists of max 55 info bits seperarted by commas.
The first line contains the 55 headers seperated by commas and all of the
remaining
long line list the data. I need to remove all data from each line except the
combination of the first and third element of data, a comma, and then the
fifth data item. Say the data lines look like this:

Adam, Martyn, Smith, pal, (e-mail address removed), bla, bla, bla,......
Frank, , Mandy, friend, (e-mail address removed), bla, bla,.....
.......

I need to achieve this as my *.csv file:
Adam Smith, (e-mail address removed)
Frank Mandy, (e-mail address removed)
.....
What is the best way to do it?
Regards

 

[Joe HM]

Hello -

I just threw some code together ... there are no error checks in case
the format is different. It will step through Column A from Row 1
until it finds an empty Row. The output will be put next to it in
Column D.

Sub extract()
Set lSheet = ThisWorkbook.Sheets("Sheet1")

Set lSEL = lSheet.Cells(1, 1)

Do While lSEL.Value <> ""

' First Name

lPositionA = InStr(lSEL.Value, ",")

lFirstName = Trim(Mid(lSEL.Value, 1, lPositionA - 1))

' Skip Middle Name

lPositionA = InStr(lPositionA + 1, lSEL.Value, ",")
lPositionA = lPositionA + 1

' Last Name

lPositionB = InStr(lPositionA, lSEL.Value, ",")

lLastName = Trim(Mid(lSEL.Value, lPositionA, lPositionB -
lPositionA))

' Skip

lPositionA = InStr(lPositionB + 1, lSEL.Value, ",")
lPositionA = lPositionA + 1

' e-mail

lPositionB = InStr(lPositionA + 1, lSEL.Value, ",")

lEmail = Trim(Mid(lSEL.Value, lPositionA, lPositionB -
lPositionA))

' OUTPUT in Column E (= 5)

lSheet.Cells(lSEL.Row, 5).Value = lFirstName & " " & lLastName
& ", " & lEmail

Set lSEL = lSheet.Cells(lSEL.Row + 1, 1)
Loop
End Sub

Enjoy,
Joe

 

[J_J]

Thanks Joe HM,
I'll give it a try and get back here...

 

[J_J]

Hi Joe HM,
Altering your code a little bit I managed to split name and email address
info into two columns as below.
Just a small problem remaining...All of the email address info contains "
symbols at the beginning and at the end of data. The name info also
sometimes have " symbols as content. I'd like to remove " characters
while transferring data to column 5 and 6.
Is that an easy task ?
Regards
J_J

'---------------------------
Sub extract()
Set lSheet = ThisWorkbook.Sheets("Sheet1")

Set lSEL = lSheet.Cells(1, 1)

Do While lSEL.Value <> ""

' First Name

lPositionA = InStr(lSEL.Value, ",")

lFirstName = Trim(Mid(lSEL.Value, 1, lPositionA - 1))

' Skip Middle Name

lPositionA = InStr(lPositionA + 1, lSEL.Value, ",")
lPositionA = lPositionA + 1

' Last Name

lPositionB = InStr(lPositionA, lSEL.Value, ",")

lLastName = Trim(Mid(lSEL.Value, lPositionA, lPositionB -
lPositionA))

' Skip

lPositionA = InStr(lPositionB + 1, lSEL.Value, ",")
lPositionA = lPositionA + 1

' e-mail

lPositionB = InStr(lPositionA + 1, lSEL.Value, ",")

lEmail = Trim(Mid(lSEL.Value, lPositionA, lPositionB - lPositionA))

' OUTPUT in Column E (= 5)

lSheet.Cells(lSEL.Row, 5).Value = lFirstName & " " & lLastName
lSheet.Cells(lSEL.Row, 6).Value = lEmail

Set lSEL = lSheet.Cells(lSEL.Row + 1, 1)
Loop
End Sub
'--------------------------------------------------

 

[Joe HM]

Hello -

This should work if you want to replace ALL "s ...

lSheet.Cells(lSEL.Row, 5).Value = Replace(lFirstName & " " & lLastName,
Chr(34), "")
lSheet.Cells(lSEL.Row, 6).Value = Replace(lEmail, Chr(34), "")

Joe

 

[J_J]

Did it!
Thanks a lot Joe HM