Howdy again folks!
I am looking to have a macro take a string of random text, find a specific word in it (which will not always be in the same place) and IF it finds it, to return the characters in the four spaces before it. I have a feeling I need to write a function or UDF for this but I don't know how yet....
What I have so far....
I have a snippet of text
*example*
"01-AUG-17 08:38:37 TRUMSL SF NEW CUST OLD NULL "
This text will not always be the same, I am trying to pull the two character text from before "NEW CUST", in this case "SF"... Hope that makes sense....

Lol. For some reason I end up answering all your questions.
Will give proper answer tomorrow mornings. Almost 1am in South Africa.

Question: is the composition the same: date, time, 2 strings then new customer text?
If yes you'd have to resort to regular expressions.

Best sourfe is analystcave.com.
That dude is a genius, his knowledge and approach humbles even the self proffessed master I am. 😂

__________________
Nostalgia 4 Infinity

Unfortunately the string will vary in length and structure, which is why this is so complicated... I am pulling a range of data from a separate program, "attachmate" and it pastes it all into one cell which makes it more difficult to work with. If I manually highlight and copy and paste it between programs, it pastes into mutliple cells almost like using text to columns. I tried looking into how to use the clipboard to make it easier by splitting into cells, but I can't get it to work with the attachmate program.
Any ideas?

I appreciate you at least reading all my questions! I have multiple macros I am writing for multiple projects, and I keep hitting these little road blocks!
I am trying to get through the entire 24 hour trainer book too but I am at a point where it is getting a little more complex and I need to spend more time with the demos and examples to actually understand it... :(
I really like writing the macros, though I can only imagine how much neater my code will be even just a year from now!

Appreciated bro, I enjoy helping.

Now, I assume you'll always read the 2 strings before the search string.
Answer is regular expressions.
Refer to the site: analystcave.com

I don't have any readily available code to provide an example.

__________________
Nostalgia 4 Infinity

Function FindDigits(myRegExp As RegExp, mySentence As String, myMin As Long, myMax As Long)

Dim myMatches As Variant

Dim myTemp() As String, myStrip As String

Dim myCount As Integer



myCount = 0

Set myMatches = myRegExp.Execute(mySentence)



For Each myMatch In myMatches

myStrip = StripNonDigits(myMatch.Value)

If CLng(myStrip) >= myMin And CLng(myStrip) <= myMax Then

ReDim Preserve myTemp(myCount)

myTemp(myCount) = myStrip

myCount = myCount + 1

End If

Next



' Debug.Print myTemp & ":" & myCount

FindDigits = myTemp



End Function

__________________
Nostalgia 4 Infinity

Function StripNonDigits(myInput As String) As String

Dim myRegExp As RegExp

Dim myMatches As Variant

Dim myTemp As String, myReplacement As String

Dim myCount As Integer



Set myRegExp = New RegExp

myReplacement = ""



' myRegExp.Pattern = "[A-Za-z]\p{L}\p{M}\p{P}\p{S}\p{Z}\p{C}"

myRegExp.Pattern = "\D+"

myRegExp.IgnoreCase = True 'True to ignore case

myRegExp.Global = True 'True matches all occurances, False matches the first occurance

myRegExp.MultiLine = True



myTemp = myRegExp.Replace(myInput, myReplacement)

StripNonDigits = myTemp



End Function

__________________
Nostalgia 4 Infinity

Sub MasterClass()

Dim myWbName As String, myWsName As String, myRngName As String, myRegExtInput As String, myFindWhat As String, myReplaceWith As String

Dim myUpdateCol As Integer, myDiscriptionCol As Integer, myIncidentCol As Integer, myMasterCol As Integer, myCounter As Integer

Dim myLenMin As Long, myLenMax As Long, myIncMin As Long, myIncMax As Long, myEnd As Long, i As Long, j As Long

Dim myWB As Workbook, myWS As Worksheet

Dim myReplaceLookIn As XlFindLookIn

Dim myRegEx As RegExp

Dim myTest As Boolean

Dim myDigits As Variant



myWbName = "Repeat Callers - Description Added 2 - Macr"

myWsName = "Repeat Callers - Description Ad"

myRngName = "A:A"



myReplaceLookIn = xlValues

myUpdateCol = 1



Set myWB = Application.Workbooks(myWbName)

Set myWS = myWB.Worksheets(myWsName)

Set myRegEx = New RegExp



myDiscriptionCol = 19

myIncidentCol = 1

myMasterCol = 2



myRegEx.Pattern = "[^0-9](\d{8})[^0-9]"

myRegEx.IgnoreCase = True 'True to ignore case

myRegEx.Global = True 'True matches all occurances, False matches the first occurance

myRegEx.MultiLine = True



myEnd = myWS.Range("A1").End(xlDown).Row

Set myRangeSearch = myWS.Range("A2:A" & myEnd)



myIncMin = Application.WorksheetFunction.Min(myRangeSearch)

myIncMax = Application.WorksheetFunction.Max(myRangeSearch)



For i = 2 To myEnd

myRegExtInput = myWS.Cells(i, myDiscriptionCol).Text

myTest = myRegEx.Test(myRegExtInput)



' myDigits = Nothing

If myTest = True Then



myDigits = FindDigits(myRegEx, myRegExtInput, myIncMin, myIncMax)



On Error Resume Next

If myDigits(0) Is Nothing Then

For Each myDigit In myDigits

myFindWhat = myDigit

myReplaceWith = myDigit

FindAndReplaceAll myWbName, myWsName, myRngName, myFindWhat, myReplaceWith, ReplaceLookInType:=myReplaceLookIn, OffSetCol:=myUpdateCol

myWS.Cells(i, myMasterCol).Select

myWS.Cells(i, myMasterCol) = myFindWhat



Next

End If

End If

'ErrHandler:

Next

End Sub

__________________
Nostalgia 4 Infinity

These were scenarios where I needed to extra a specific pattern & composition of digits and text was of arbitrary length & composition.
Had to run it on about 200k records and do a inner comparison on the same number of records.

__________________
Nostalgia 4 Infinity