thandel
asked on
How can I replace text but only looking for a string along ending with an alapha character
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.
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.
ASKER
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?
Isn't there something like using the replace command with [!0123456789] perhaps?
ASKER
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','- ','')
ASKER
I don't follow... how would this check for various dates?
can you please specify
various dates in same row or number of rows
various dates in same row or number of rows
ASKER
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
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
ASKER
Any assistance for this question?
ASKER
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.
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)
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))
Is each entry in the memo field separated by a CR/LF?
Generally speaking, you could use
if not(isnumeric(Character Value))
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
Hmm, I suppose it was quite a while I got called away before submitting.
Steve
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.Reg Exp")
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
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.Reg
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
10/5 - Customer called back, didn't want to place the order 10/6 - Customer changed their minds and would like to place order
ASKER
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.
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
~bp
http://bytes.com/topic/sql-server/answers/591852-sql-replace-replace