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]
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

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,
 

Forum statistics

Threads
1,141,927
Messages
5,709,394
Members
421,634
Latest member
Spicoli21

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top