Macro Find and Search

[SH Harbour]

Hi, I have a worksheet which I need a Macro to search through column E2:E and find the info between #~ and 954#N and then put the relevant info in the corresponding cell F, an example of the info is as follows:

I would like it to appear as follows:

I can produce a formula which produces the result as follows:
= Mid(E2, Search("#~~", E2) + 1, Search("#954#N", E2, Search("#~", E2) + 1) - Search("#~~", E2) - 1)

However would rather have a Macro that could perform the operation, any assistance would be greatly appreciated?

Thanks

 

[Joe4]

Welcome to the Board!

Since you are using Excel 365, you can do this really easily with some of the new functions, i.e.

=TEXTBEFORE(TEXTAFTER(E2,"#~"),"954#N")

Do you still need/want VBA?
If so, perhaps look at using the SPLIT function in VBA: MS Excel: How to use the SPLIT Function (VBA)

 

[SH Harbour]

Welcome to the Board!

Since you are using Excel 365, you can do this really easily with some of the new functions, i.e.

=TEXTBEFORE(TEXTAFTER(E2,"#~"),"954#N")

Do you still need/want VBA?
If so, perhaps look at using the SPLIT function in VBA: MS Excel: How to use the SPLIT Function (VBA)

Hi

Thankyou

Yes the macro / VBA option would be best as the the report generated changes repeatedly and would rather not have to enter each time as i have a personal workbook open in the background so can run the relevant macro on a daily basis through hundreds of lines of data

Thanks

 

[Joe4]

So, are you wanting to replace the value in place, or place the result in another cell?
If in another cell, is it always the one to the right of the cell?

 

[Joe4]

Assuming that you want it to replace the current values, you can use this code.
Simply select the range you want to apply it to and run the code:

Sub FixValues()
    
    Dim cell As Range
    Dim str As String
    Dim ar1() As String
    Dim ar2() As String
    
    Application.ScreenUpdating = False
    
'   Loop through all selected cells
    For Each cell In Selection
        If (InStr(1, cell, "#~") > 0) And (InStr(1, cell, "954#N") > 0) Then
            str = cell
            ar1 = Split(str, "#~")
            ar2 = Split(ar1(1), "954#N")
            cell = ar2(0)
        End If
    Next cell
    
    Application.ScreenUpdating = True
    
End Sub

 

[SH Harbour]

Good Evening

Yes to place in another cell, always to the right of the cell I am taking the data from.

Thanks

 

[SH Harbour]

Thanks I will give this a go, was having issues with the Tilda ~

Thanks

 

[Joe4]

Minor adjustment then:

Sub FixValues()
    
    Dim cell As Range
    Dim str As String
    Dim ar1() As String
    Dim ar2() As String
    
    Application.ScreenUpdating = False
    
'   Loop through all selected cells
    For Each cell In Selection
        If (InStr(1, cell, "#~") > 0) And (InStr(1, cell, "954#N") > 0) Then
            str = cell
            ar1 = Split(str, "#~")
            ar2 = Split(ar1(1), "954#N")
            cell.Offset(0, 1) = ar2(0)
        End If
    Next cell
    
    Application.ScreenUpdating = True
    
End Sub

Note: You could simply have VBA apply the formula I posted in my first response in all those cells too.