• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

inddon

Member
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
 
Hello, according to Excel / VBA basics a loop is totally useless : l_Paragraph = Replace(l_Paragraph, ":x_", "#!LAPS!#x_") …​
 
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)))
80852
 
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
 
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
 

Attachments

  • Sample Workbook (Search List and Replace).xlsm
    23.4 KB · Views: 6
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
 
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 :awesome:
 
According to your post #5 attachment and according to post #2 - so according to Excel / VBA basics - a demonstration for starters :​
Code:
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 !​
 
According to your post #5 attachment and according to post #2 - so according to Excel / VBA basics - a demonstration for starters :​
Code:
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




80884



Regards,
Don
 
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 :):awesome:

Thank you @Marc L
 
Back
Top