Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing section of a string in VBA
It worked like a charm.... Thanks for the quick response
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Removing character in a string | Excel Discussion (Misc queries) | |||
removing value within a string | Excel Worksheet Functions | |||
Please help with removing text string | Excel Discussion (Misc queries) | |||
Removing Spaces from string | Excel Worksheet Functions | |||
removing certain text from a string | Excel Programming |