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

Check this link


http://bytes.com/topic/sql-server/answers/591852-sql-replace-replace

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?

I am not familiar with SQL are they any suggestions using VBA?

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','- ','')

Select allOpen in new window

I don't follow... how would this check for various dates?

can you please specify
various dates in same row or number of rows

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

Any assistance for this question?

Experts?

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.

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))

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

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

Select allOpen in new window

Hmm, I suppose it was quite a while I got called away before submitting.

Steve

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

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.

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

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.

Did you see my post ?

Fair enough, I missed the bit about being in the middle of a line too... no passing that 1m mark while I sleep Bill!.

I don't see 10k dropping in my lap tonight Steve, rest easy...

~bp