[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How can I replace text but only looking for a string along ending with an alapha character

Posted on 2011-10-13
24
Medium Priority
?
436 Views
Last Modified: 2013-12-20
I have a memo field that might may have the following text formatted as:

MM/DD - BLABLABLA (For ex. 10/1 - This is a test)

The memo field may have additional text and this format might be repeated just with different dates.  I need to remove the "- " but I have to make sure I get this accurately.  I can if I can search for "/" followed by a one or two digit number then a space then "-" then a space then any alpha character.

I was thinking about using a loop and and midstring to work through the memo field.  The only issues is that I can't figure out how to search for an alpha character or perhaps any character that is not a number following the " - ".

Any suggestions?

Thank you.
0
Comment
Question by:thandel
  • 7
  • 3
  • 3
  • +5
22 Comments
 
LVL 9

Expert Comment

by:sachinpatil10d
ID: 36962734
0
 

Author Comment

by:thandel
ID: 36964215
I am sorry but I don't see how I can apply that to VBA to alter text in a memo field.

Isn't there something like using the replace command with [!0123456789] perhaps?
0
 

Author Comment

by:thandel
ID: 36970289
I am not familiar with SQL are they any suggestions using VBA?
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 9

Expert Comment

by:sachinpatil10d
ID: 36978003
Try this




select REPLACE('10/1 - This is a test,'- ','')

for multiple occurrences 
select REPLACE('10/1 - This is a test 10/1 - This is a test 10/11- This is a test','- ','')

Open in new window

0
 

Author Comment

by:thandel
ID: 36982765
I don't follow... how would this check for various dates?
0
 
LVL 9

Expert Comment

by:sachinpatil10d
ID: 36991346
can you please specify
various dates in same row or number of rows
0
 

Author Comment

by:thandel
ID: 36992956
This is a memo field on a form.  

Ex.
10/1 - Called customer for Rx -10.00 -0.25 x125
10/5 - Customer called back, didn't want to place the order 10/6 - Customer changed their minds and would like to place order
10/7 - Order placed Ref #12345

I would like the "-" to be removed when only afte a date as some values need the "-". Which is why I was looking for a date followed by a "-" then an alpha character.  I would like the updated field to read:

10/1 Called customer for Rx -10.00 -0.25 x125
10/5 Customer called back, didn't want to place the order 10/6 Customer changed their minds and would like to place order for -11.50 SPH OU
10/7 Order placed Ref #12345
0
 

Author Comment

by:thandel
ID: 37033601
Any assistance for this question?
0
 

Author Comment

by:thandel
ID: 37058159
Experts?
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 37103942
Hi THandel

It seems that you want to remove the hyphen near the beginning of a text string when preceeded by something that looks like a date.

One VB block to handle this could be:

Sub x()
    Dim iHyphenLocation As Integer
    Dim strTextLine As String
    Dim strPossibleDate As String
    Dim strDatePieces() As String
   
    strTextLine = "10/1 - blablablah"
   
    iHyphenLocation = InStr(strTextLine, "-")
    If iHyphenLocation > 0 Then
      strPossibleDate = Trim(Left(strTextLine, iHyphenLocation - 1))
      strDatePieces = Split(strPossibleDate, "/")
      If UBound(strDatePieces) > 0 Then
         If IsNumeric(strDatePieces(0)) And IsNumeric(strDatePieces(1)) Then
           Mid(strTextLine, iHyphenLocation, 1) = "x" ' replace hyphen with desired character
        End If
      End If
    End If
End Sub

You could turn this into a function if you wanted.
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 37103993
Is this MS Access?
Is each entry in the memo field separated by a CR/LF?
Generally speaking, you could use
if not(isnumeric(Character Value))
0
 
LVL 59

Accepted Solution

by:
Bill Prew earned 2000 total points
ID: 37104021
I think regular expressions are the best way to do this, here's a small VBS script that demonstrates the approach.  Save as a VBS and run like:

cscript EE27395011.vbs

strMemo = "10/1 - Called customer for Rx -10.00 -0.25 x125" & vbCrLf & _
          "10/5 - Customer called back, didn't want to place the order 10/6 - Customer changed their minds and would like to place order" & vbCrLf & _
          "10/7 - Order placed Ref #12345" & vbCrLf & _
          "10/17 - Order placed Ref #12345" & vbCrLf & _
          "1/7 - Order placed Ref #12345"

'Set objRegExp = New RegExp
Set objRegExp = CreateObject("vbscript.regexp") 
objRegExp.Global = True
objRegExp.IgnoreCase = False
objRegExp.Pattern = "([0-9]+/[0-9]+) - ([a-zA-Z])"
strNew = objRegExp.Replace(strMemo, "$1 $2")

Wscript.Echo "**** BEFORE ****" & vbCrLf & strMemo
Wscript.Echo "**** AFTER ****" & vbCrLf & strNew

Open in new window

Output generated by this example:

**** BEFORE ****
10/1 - Called customer for Rx -10.00 -0.25 x125
10/5 - Customer called back, didn't want to place the order 10/6 - Customer changed their minds and would like to place order
10/7 - Order placed Ref #12345
10/17 - Order placed Ref #12345
1/7 - Order placed Ref #12345

**** AFTER ****
10/1 Called customer for Rx -10.00 -0.25 x125
10/5 Customer called back, didn't want to place the order 10/6 Customer changed their minds and would like to place order
10/7 Order placed Ref #12345
10/17 Order placed Ref #12345
1/7 Order placed Ref #12345

Open in new window

~bp
0
 
LVL 44

Expert Comment

by:GRayL
ID: 37104041
Where did " for -11.50 SPH OU"  come from in the 'what you would like' format?
0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 37104149
OK doesn't sounds too bad.  I assume you have pulled the field out into a variable already?  Here I am assuming a "-" in the first 10 chars should cover it rather than checking specifically for numeric / text either side?

Steve

 
Rem Get text from cell A1 into string "memo"
memo = Range("A1").Text

Rem split the lines out into an array
MemoSplit = Split(memo, Chr(10))

Rem work down the array.
For x = LBound(MemoSplit) To UBound(MemoSplit)
    Rem Look in first 10 chars for a "-"
    DashPos = InStr(Left(MemoSplit(x), 10), "-")
    If DashPos > 0 Then
        Rem If - was found take the part before it and after it stripped of trailing / leading spaces
        MemoSplit(x) = Trim(Left(MemoSplit(x), DashPos - 1)) & " " & Trim(Mid(MemoSplit(x), DashPos + 1))
    End If
Next
Rem Make string from the array
Memo2 = Join(MemoSplit, Chr(10))
Rem Add it back into cell A2
Range("A2") = Memo2

Open in new window

0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 37104161
Hmm, I suppose it was quite a while I got called away before submitting.

Steve
0
 
LVL 19

Expert Comment

by:Shahan Ayyub
ID: 37104202
Hi!

You should look at these links:

For SQL:
http://msdn.microsoft.com/en-us/magazine/cc163473.aspx
http://www.simple-talk.com/sql/t-sql-programming/tsql-regular-expression-workbench/

For VBA:
http://www.tek-tips.com/viewthread.cfm?qid=1441918

a relevant and altered code from the above link: (not tested)

   Function RemoveFirstHyphen(ByVal str As String) As String
        Dim RegEx As Object
        RegEx = CreateObject("vbscript.RegExp")
        With RegEx
            .Global = False      '  Replace only first occurance
            .Pattern = "\s\-"     '  since you have format "MM/DD - BL...." so remove first hyphen and a space from the input string
        End With
        RemoveFirstHyphen = RegEx.Replace(str, "")
        RegEx = Nothing
    End Function
0
 
LVL 44

Expert Comment

by:GRayL
ID: 37104428
It looks like '/' will always be in the first six or seven characters of each memo field.  The one in question seems to be down there around the 45the character or so.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 37104432
thandel:  In this memo field (second of your examples), are you talking about the first slash at posn 3 or the last one?

10/5 - Customer called back, didn't want to place the order 10/6 - Customer changed their minds and would like to place order
0
 

Author Comment

by:thandel
ID: 37117679
Sorry for the late replies:

Gray - any date entry followed by " - " I would like to have it removed.... so it could be any where in the field.  However I need to ensure that something like -10.50 sph does lost the "-".

"Where did " for -11.50 SPH OU"  come from in the 'what you would like' format? " 

"10/5 Customer called back, didn't want to place the order 10/6 Customer changed their minds and would like to place order for -11.50 SPH OU" - Should remain unchanged.


billprew's approach looks good I need to test it.

0
 
LVL 19

Expert Comment

by:Shahan Ayyub
ID: 37117703
Did you see my post ?
0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 37118074
Fair enough, I missed the bit about being in the middle of a line too... no passing that 1m mark while I sleep Bill!.
0
 
LVL 59

Expert Comment

by:Bill Prew
ID: 37118117
I don't see 10k dropping in my lap tonight Steve, rest easy...

~bp
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question