Left, Right Split String in a cell

[bogie81]

Hi Everyone.

I need some help.

I have a list of collage organization with over 1200 rows. What I'm trying to do is delete blank rows and rows with a columns("A") that start with "Purpose:".
Up to here it works fine but I'm stuck on spliting my cell string nito multiple cells.

Here's the sample cell in Column A.
"President: Constantine Yannelis, yannelis@uiuc.edu"

What I'm trying to do is move "President" into cell.offset(1,-1) - one column to the right and one row up. It's always "President" so maybe we just could use a variable.

Name " Constantine Yannelis" should go into cell.offset(2,-1)
e-mail "yannelis@uius.edu = cell.offset(3,-1).

The range object is not set corectly but I don't really worry about it right now.

Sub format()
Dim myrange As Range
Dim cell As Range

Set myrange = Range("a709", Range("A1105"))

Dim SearchString As String

myrange.Select
For Each cell In myrange
    
    cell.Select
    
    If cell = "" Then
        cell.Select
        
        cell.EntireRow.Delete
    ElseIf Left(cell, 8) = "Purpose:" Then
        cell.EntireRow.Delete

    ElseIf Left(cell, 10) = "President:" Then
        
MISSING CODE.
        
       End If
Next cell

    
End Sub

Thanks for all your help.[/list][/b]

 

[Scott Huish]

First, when you're deleting rows, you want to move backward through the range, not forward.
It's also not generally necessary to select cells in order to work with them.

Did you want to move the whole cell value that starts with the word President or just the word President and delete it from the original cell? If the whole cell, does this row then get deleted too?

 

[bogie81]

The original cell value in column A that starts with "President" will be deleted after the "President", Name and e-mail (working e-mail link) are moved to other cells as explained in the previous post.

I'm just having a hard time understanding left right split functions. For example: Let's say I want to extract the e-mail from the cell. I know I have to use the left() function but i'm not sure how to indicate get everything from left till you get to space instead of length of that string
Left(cella1, 8).

Thanks,