Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 23rd, 2017, 05:22 PM
Registered User
 
Join Date: Oct 2017
Posts: 11
Thanks: 1
Thanked 0 Times in 0 Posts
Default Find text within a cell, and pull four characters previous

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....
Code:
    
CSHSData = sess.screen.area(6, 2, 23, 80)

    If InStr(CSHSData, "NEW CUST") <> 0 Then
       text = CSHSData
    result = Mid(text, 1, InStr(1, text, "NEW CUST"))
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....
 
Old October 23rd, 2017, 06:56 PM
Authorized User
 
Join Date: Oct 2015
Posts: 48
Thanks: 0
Thanked 5 Times in 5 Posts
Cool

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
 
Old October 25th, 2017, 01:24 PM
Registered User
 
Join Date: Oct 2017
Posts: 11
Thanks: 1
Thanked 0 Times in 0 Posts
Default

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?
 
Old October 25th, 2017, 01:30 PM
Registered User
 
Join Date: Oct 2017
Posts: 11
Thanks: 1
Thanked 0 Times in 0 Posts
Default

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!
 
Old October 25th, 2017, 03:25 PM
Authorized User
 
Join Date: Oct 2015
Posts: 48
Thanks: 0
Thanked 5 Times in 5 Posts
Default

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
 
Old October 25th, 2017, 04:24 PM
Authorized User
 
Join Date: Oct 2015
Posts: 48
Thanks: 0
Thanked 5 Times in 5 Posts
Default

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
 
Old October 25th, 2017, 04:27 PM
Authorized User
 
Join Date: Oct 2015
Posts: 48
Thanks: 0
Thanked 5 Times in 5 Posts
Default

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
 
Old October 25th, 2017, 04:30 PM
Authorized User
 
Join Date: Oct 2015
Posts: 48
Thanks: 0
Thanked 5 Times in 5 Posts
Default

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
 
Old October 25th, 2017, 04:34 PM
Authorized User
 
Join Date: Oct 2015
Posts: 48
Thanks: 0
Thanked 5 Times in 5 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy more than 1024 characters to a cell justinferns Excel VBA 2 September 13th, 2011 07:36 AM
Displaying last 8 characters in a cell scandalous Excel VBA 1 December 21st, 2007 11:04 PM
Find previous page gandaliter PHP How-To 2 June 7th, 2007 11:55 AM
Lose cell Text when editing cell in VSFlexGrid 6 bobcratchet VB How-To 0 July 30th, 2004 09:32 AM
reading first characters from a cell in excel vdriet Excel VBA 2 August 17th, 2003 10:10 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.