VBA versus Worksheet function
 

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

VBA versus Worksheet function
 

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


"Prashant" wrote in message
...
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




----== 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 =---

VBA versus Worksheet function
 

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" wrote in message
...
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


"Prashant" wrote in message
...
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




----== 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
=---




----== 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 =---