VBA versus Worksheet function

  • Thread starter Thread starter Prashant
  • Start date Start date
P

Prashant

Friends,
I am trying to manipulate string in a column. The input
column is represented by the following values:
I_need_This_aaa
I_need_This_bbb I_dont_need_this_nnn
I_dont_need_this_xxx "I_need_this_ccc" not_this_yyy

the resultant column i am needing should look something
like this:
I_need_This_aaa
I_need_This_bbb
I_need_this_ccc

As my example represents, the initial few characters of
the required string are always common. And i have 1000s
of rows in this column.

So, can you help me out device a method to extract the
string either using a worksheet function or a VBA script.

Appreciate your help.

Prashant
 
Try this code,which you may wish to adapt. It is set to read the first
10000 rows of column A.
Cheers
Nigel

Public Sub NeedThis()
Dim ir As Long
Dim VBegin As Integer, VEnd As Integer
Dim InString As String, OutString As String
For ir = 1 To 10000
InString = RTrim(Worksheets("Sheet1").Cells(ir, 1))
If Len(InString) > 0 Then
VBegin = InStr(UCase(InString), "I_NEED_THIS")
If VBegin > 0 Then
VEnd = InStr(VBegin, InString, " ")
If VEnd = 0 Then VEnd = Len(InString)
OutString = Mid(InString, VBegin, VEnd - VBegin + 1)
Else
OutString = ""
End If
Worksheets("Sheet1").Cells(ir, 1) = OutString
End If
Next ir
End Sub
 
I should have said it only detects the first instance of i_need_this in
each row, if there are more then you will need to modify the code to suit.
Cheers
N

Nigel said:
Try this code,which you may wish to adapt. It is set to read the first
10000 rows of column A.
Cheers
Nigel

Public Sub NeedThis()
Dim ir As Long
Dim VBegin As Integer, VEnd As Integer
Dim InString As String, OutString As String
For ir = 1 To 10000
InString = RTrim(Worksheets("Sheet1").Cells(ir, 1))
If Len(InString) > 0 Then
VBegin = InStr(UCase(InString), "I_NEED_THIS")
If VBegin > 0 Then
VEnd = InStr(VBegin, InString, " ")
If VEnd = 0 Then VEnd = Len(InString)
OutString = Mid(InString, VBegin, VEnd - VBegin + 1)
Else
OutString = ""
End If
Worksheets("Sheet1").Cells(ir, 1) = OutString
End If
Next ir
End Sub







----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption
Click to expand...
=---
 
Nigel,

Thanks a lot. The method works. But is there something
that I can use the way we use the built in functions?
(without having to go to the VBA script each time for
running). Something that gets updated every time the
input cell changes.

Appreciate your help.

Prashant
 
Back
Top