Search for a character/string, get the location's whole word..

[inddon]

Hello There,

I would like to have a VBA Sub which would do the following:

Below varaibles:

a. l_Paragraph =
"A string is any series of characters that are interpreted literally by a script. :x_For example, hello world :x_and LKJH019283 are both examples of strings. In computer :x_programming, a string is attached to a variable as :x_shown in the example below."

b. l_SearchString = ":"

The paragraph block can have multiple lines (with enter signs).

1. Loop till True
............a. in variable l_paragraph, search for the 1st occurence of the string (l_SearchString)
............b. When found
........................i. Then list it's whole word in the worksheet starting with Range A1 and so on
........................ii. Replace the searched character/string found ":" with "#!LAPS!#"
........................iii. True
........................iv. Loop for next occurence
............c. When Not Found then False, out of Loop


Example Output:
Sheet1 A1 = x_For
Sheet1 A2 = x_and
Sheet1 A3 = x_programming (This word is having a ',' at the end, should be excluded)
Sheet1 A4 = x_shown

l_paragraph =
"A string is any series of characters that are interpreted literally by a script. #!LAPS!#x_For example, hello world #!LAPS!#x_and LKJH019283 are both examples of strings. In computer #!LAPS!#x_programming, a string is attached to a variable as #!LAPS!#x_shown in the example below."

Thank you and look forward to hearing from you


Regards,
Don

 

[Marc L]

Hello, according to Excel / VBA basics a loop is totally useless : l_Paragraph = Replace(l_Paragraph, ":x_", "#!LAPS!#x_") …​

​

​

 

[p45cal]

Does it have to be VBA?
Office 365:

Example Output:
Sheet1 A1 = x_For
Sheet1 A2 = x_and
Sheet1 A3 = x_programming (This word is having a ',' at the end, should be excluded)
Sheet1 A4 = x_shown

=BYROW(DROP(TEXTSPLIT(C2,,":"),1),LAMBDA(a,LEFT(a,MIN(IFERROR(SEARCH({" ",","},a),FALSE))-1)))

 

[inddon]

Does it have to be VBA?
Office 365:


=BYROW(DROP(TEXTSPLIT(C2,,":"),1),LAMBDA(a,LEFT(a,MIN(IFERROR(SEARCH({" ",","},a),FALSE))-1)))
View attachment 80852

Thank you p45cal. The formula would be useful. I will kepp this in mind.

For my requirement it has to be in VBA. I will post a sample workbook soon as requestd by Marc

Regards,
Don

 

[inddon]

Hello, according to Excel / VBA basics a loop is totally useless : l_Paragraph = Replace(l_Paragraph, ":x_", "#!LAPS!#x_") …​

​

​

Hello @Marc L, @p45cal

Thank you for your repsonse.

Attached a sample workbook with the explanation for your reference.

Regards,
Don

 

[p45cal]

Is this an assignment we're doing for you?

 

[inddon]

Is this an assignment we're doing for you?

Hello p45cal,

All I know is, I am seeking your help.

I wrote the code and it turned out to be too lengthy & messy one. Discarded it at the end.

I am looking forward for a smarter way to get this working and learn too. That is why the post in the forum (with detail description).

Regards,
Don

 

[p45cal]

All I know is, I am seeking your help.

So it is an assignment. When does it have to be done by?

 

[inddon]

So it is an assignment. When does it have to be done by?

If you consider this as an assignment then I will buy you some beer (if it is okay with you)
I should have solved it by yesterday. But whenever you have time available and can look into it, that would be wonderful

 

[Marc L]

According to your post #5 attachment and according to post #2 - so according to Excel / VBA basics - a demonstration for starters :​

Sub Demo1()
        Dim V, L&
    With Sheet2
        .UsedRange.Clear
    With Sheet1
        V = Filter(Split(.[C3]), .[C9], True):  If UBound(V) = -1 Then Beep: Exit Sub
    For L = 0 To UBound(V)
        While V(L) Like "[!A-z]*":  V(L) = Mid(V(L), 2):               Wend
        While V(L) Like "*[!A-z]":  V(L) = Left(V(L), Len(V(L)) - 1):  Wend
    Next
        ReDim Preserve V(UBound(V) + 2)
        V(UBound(V)) = Replace(.[C3], .[C9], .[H9])
    End With
        .[A1].Resize(UBound(V) + 1) = Application.Transpose(V)
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[inddon]

According to your post #5 attachment and according to post #2 - so according to Excel / VBA basics - a demonstration for starters :​

Sub Demo1()
        Dim V, L&
    With Sheet2
        .UsedRange.Clear
    With Sheet1
        V = Filter(Split(.[C3]), .[C9], True):  If UBound(V) = -1 Then Beep: Exit Sub
    For L = 0 To UBound(V)
        While V(L) Like "[!A-z]*":  V(L) = Mid(V(L), 2):               Wend
        While V(L) Like "*[!A-z]":  V(L) = Left(V(L), Len(V(L)) - 1):  Wend
    Next
        ReDim Preserve V(UBound(V) + 2)
        V(UBound(V)) = Replace(.[C3], .[C9], .[H9])
    End With
        .[A1].Resize(UBound(V) + 1) = Application.Transpose(V)
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

Hello @Marc L

Thank you for taking the time for the code. Never used Wend, good to know.

I executed the code, it displays the second part of the output correct.
The first part gets missed out (attached a printscreen)
Please advise








Regards,
Don

 

[inddon]

Hello @Marc L

Thank you for taking the time for the code. Never used Wend, good to know.

I executed the code, it displays the second part of the output correct.
The first part gets missed out (attached a printscreen)
Please advise




Regards,
Don

Please ignore my previous post (11), my mistake The code works perfect

Thank you @Marc L