Left, Right Split String in a cell

bogie81

New Member
Joined
Aug 23, 2006
Messages
6
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.


Code:
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]
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Aug 23, 2006
Messages
6
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,
 

Watch MrExcel Video

Forum statistics

Threads
1,113,983
Messages
5,545,340
Members
410,678
Latest member
glasi
Top