Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...
MS Office Forum / Excel / Programming / January 2006

Tip: Looking for answers? Try searching our database.

Need help parsing a string

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Daminc - 26 Jan 2006 10:05 GMT
I've exported a file from Outlook into excel and used this code:

Code:
--------------------
   Sub removelinebreaks()
 
 Dim lastrow As Long, i As Long
 Dim first As String, second As String
 
 lastrow = Cells(Rows.Count, 2).End(xlUp).Row
 For i = 2 To lastrow
 
 first = Cells(i, "B").Select
 ActiveCell.Value = WorksheetFunction. _
 Substitute(ActiveCell.Value, Chr(10), Chr(0))
 Next i
 
 End Sub
--------------------

To get rid of any line spaces and I'm left with:

Code:
--------------------
   35084#http://www.asite.co.uk#aw@asite.co.uk
--------------------

I would like to be able to parse this into 3 different columns if I
could.

Can anyone help me with this please?

Signature

Daminc

Reply to this Message
Mike Fogleman - 26 Jan 2006 11:10 GMT
Under the Data menu use Text to Columns. You will want Delimited and then
select Other and type in #. Click Finish.
Very similar to the text import wizard except your text is already imported.
Mike F

> I've exported a file from Outlook into excel and used this code:
>
[quoted text clipped - 27 lines]
>
> Can anyone help me with this please?
Reply to this Message
Daminc - 26 Jan 2006 11:34 GMT
I followed that but it didn't work as I expected.

There are nearly 2500 rows.

I selected them all and followed the instructions.
All it did was delete everything apart from the 3 numbers on lines 6,
and 8.
I thought the formatting might interfere so I formatted all the cell
to text but still no joy.

It even shows this in the preview panel.

What am I missing :confused
Reply to this Message
Daminc - 26 Jan 2006 11:53 GMT
Solved it :)

I had deleted the 'new lines' but so some reason it was reading a
'carriage return' so I created this little macro:

Code:
--------------------
   Sub removelinebreaks()
 
 Dim lastrow As Long, i As Long
 Dim first As String, second As String
 
 lastrow = Cells(Rows.Count, 2).End(xlUp).Row
 For i = 2 To lastrow
 
 first = Cells(i, "B").Select
 ActiveCell.Value = WorksheetFunction. _
 Substitute(ActiveCell.Value, Chr(10), Chr(0))
 ActiveCell.Value = WorksheetFunction. _
 Substitute(ActiveCell.Value, Chr(13), Chr(0))
 Next i
 
 End Sub
--------------------

and then applied what you said and it works now.

Thanks Mike.

Signature

Daminc

Reply to this Message
Daminc - 26 Jan 2006 11:54 GMT
Solved it :)

I had deleted the 'new lines' but so some reason it was reading a
'carriage return' so I created this little macro:

Code:
--------------------
   Sub removelinebreaks()
 
 Dim lastrow As Long, i As Long
 Dim first As String, second As String
 
 lastrow = Cells(Rows.Count, 2).End(xlUp).Row
 For i = 2 To lastrow
 
 first = Cells(i, "B").Select
 ActiveCell.Value = WorksheetFunction. _
 Substitute(ActiveCell.Value, Chr(10), Chr(0))
 ActiveCell.Value = WorksheetFunction. _
 Substitute(ActiveCell.Value, Chr(13), Chr(0))
 Next i
 
 End Sub
--------------------

and then applied what you said and it works now.

Thanks Mike.

Signature

Daminc

Reply to this Message
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage
Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.