How to get sub strings using delimiter

Solved/Closed
Report
-
 abhi -
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 !!!

3 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
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
2
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

thanks venkat...but i am getting error in the below line on running the code

j = WorksheetFunction.Search(" ", Range("a1"))
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
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
thanks again Venkat...u really saved my lot of time...thanks again !
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
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
thanks a lot Venkat...its working perfect !!!

can u please also let me know the VB code if i want to copy the middle of the string.

I have the below string:

insert_job: crew2stg job_type: c

and now I want the substring "crew2stg"

I tried using MID, but its giving me "crew2stg job_type: c "

How can I get rid of the last part so that i get the desired sub string ?