Removing section of a string in VBA
 

Hi I have a worksheet with a column of goals (for teachers) and some
directions. For example:
1) Student work samples - A sample of student work needs to be
presented and dated.
This is all in one cell
2) Parent Involvement Folder - Proof of parent consent must be
presented
and so on and so on
I just need:
Student work samples
Parent Involvement Folder

I need to carry over the goal to another sheet which I already did
based on some info but I don't need the number preceeding the goal AND
the instructions following the -
There is no set number of characters because each goal is different.

Please if anybody could point me in the right direction

Removing section of a string in VBA
 

One way:

Dim sTemp As String
Dim nPos As Long
sTemp = Sheets("Sheet1").Range("A1").Text
nPos = InStr(sTemp, ")")
If nPos 0 Then sTemp = LTrim(Mid(sTemp, nPos + 1))
nPos = InStr(sTemp, "-")
If nPos 0 Then sTemp = RTrim(Left(sTemp, nPos - 1))
Sheets("Sheet2").Range("A1").Value = sTemp

or, equivalently:

With Sheets("Sheet1").Range("A1")
Sheets("Sheet2").Range("A1").Value = _
Trim(Mid(Left(.Text, InStr(.Text, "-") - 1), _
InStr(.Text, ")") + 1))
End With



In article . com,
"Jake Wiley" wrote:

Hi I have a worksheet with a column of goals (for teachers) and some
directions. For example:
1) Student work samples - A sample of student work needs to be
presented and dated.
This is all in one cell
2) Parent Involvement Folder - Proof of parent consent must be
presented
and so on and so on
I just need:
Student work samples
Parent Involvement Folder

I need to carry over the goal to another sheet which I already did
based on some info but I don't need the number preceeding the goal AND
the instructions following the -
There is no set number of characters because each goal is different.

Please if anybody could point me in the right direction

Removing section of a string in VBA
 

Hi Jake,
If cell A1 contains "1) Student work samples - A sample of student work
needs to be presented and dated." then the following worksheet function
extracts "Student work sample" by finding the ")" and "-". It does rely
on there being a space after the ")" and before the "-":

=MID(A1,FIND(")",A1,1)+2,FIND("-",A1,1)-FIND(")",A1,1)-3)

You could use this without any VB or adapt it to your code.

Ken Johnson

Removing section of a string in VBA
 

Jake Wiley wrote:
Hi I have a worksheet with a column of goals (for teachers) and some
directions. For example:
1) Student work samples - A sample of student work needs to be
presented and dated.
This is all in one cell
2) Parent Involvement Folder - Proof of parent consent must be
presented
and so on and so on
I just need:
Student work samples
Parent Involvement Folder


I wouldn't use VBA if you just need to do it once. Create an empty column to
the right of this column. Highlight the column with the text and choose
Text To Columns from the Data menu. Split the text based on a hyphen
delimeter. Delete the new column.

--
Dick Kusleika
MVP-Excel
www.dailydoseofexcel.com

Removing section of a string in VBA
 

Try:

Sub MoveGoalData()
Dim t As String
Dim r As Range, c As Range
Dim x As Integer, y As Integer, i As Integer
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
Set c = ws1.Cells(Rows.Count, 1).End(xlUp)
Set r = ws1.Range(ws1.Cells(3, 1), c)
i = 0
For Each c In r.Cells
i = i + 1
x = InStr(c.Value, " ") + 1
y = InStr(c.Value, "-") - 1
t = Mid(c.Value, x, y - x)
ws2.Cells(i, 1).Value = t
Next
End Sub

Regards,
Greg

"Jake Wiley" wrote:

Hi I have a worksheet with a column of goals (for teachers) and some
directions. For example:
1) Student work samples - A sample of student work needs to be
presented and dated.
This is all in one cell
2) Parent Involvement Folder - Proof of parent consent must be
presented
and so on and so on
I just need:
Student work samples
Parent Involvement Folder

I need to carry over the goal to another sheet which I already did
based on some info but I don't need the number preceeding the goal AND
the instructions following the -
There is no set number of characters because each goal is different.

Please if anybody could point me in the right direction

Removing section of a string in VBA
 

It worked like a charm.... Thanks for the quick response