how to get sub strings using delimiter Solved/Closed

Question

hi,

i want to copy the part of the string in a particular cell to another sheet.

e.g. the string is cell A1 is as below:

EVENT_ID NUMBER(10) NULL

In the above string I want to copy the sub-string EVENT_ID and paste it to another worksheet.

Can anyone help me out in getting the first substring i.e EVENT_ID in the above given space delimited string or is there any function available which directly given me the first sub-string of the given string.


Thanks in advance !!!

venkat1926 · Accepted Answer

try this macro 
result is in G1


Sub test()
Dim j As Integer
j = WorksheetFunction.Search(" ", Range("a1"))
'MsgBox j
Range("G1") = Left(Range("a1"), j)

End Sub

venkat1926 · Answer

In my file the full string is in A1.  where is it yours ?
if it is not A1 changed range*"a1") in this line sutably

venkat1926 · Answer

the sub string you are interested is 
"crew2stg"

now see that there is a space before this sub string(and also this is the first space)
there is a space at the end of the sub string (which is second one)
use that to search 

the macro is this and your value is x
you must always carefully study the macro and use it in your file
the full string is in A1 according to my macro 


Sub test()
Dim r As Range, j As Integer, k As Integer
Dim x As String
Set r = Range("A1")
j = WorksheetFunction.Search(" ", r.Value)
MsgBox j
k = WorksheetFunction.Search(" ", r.Value, j + 1)
MsgBox k
x = Mid(r, j + 1, k - j)
MsgBox x
Range("G1") = x
End Sub

How to get sub strings using delimiter

3 responses

Similar discussions

Subscribe To Our Newsletter!