Hello,
I just want to change the 1st # in a column of 9 digit #s. Ex: I want to
find 712015647 and replace it with 612015647. I thought about finding
7??????? and replacing with 6???????? but it replaces my # with "6????????".
I want to keep the last 8 #s and just replace the 1st number from a 7 to a 6.
Can someone help?
Thank you,
Studebaker
If you enter the VBA Function below, you can use various patterns to handle
both the replacement in this thread, as well as in your other thread.
To enter the UDF, <alt-F11> opens the VB Editor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
code below into the window that opens.
Now I'm not sure what you want to do in the example above, but if you wanted to
find any 9 digit number that started with a 7, and replace the 7 with a 6, you
would use the following:
=rerepl(A1,"7(\d{8})","6$1")
The expression: 7(\d{8})
means:
Match the character “7” literally «7»
Match the regular expression below and capture its match into backreference
number 1 «(\d{8})»
Match a single digit 0..9 «\d{8}»
Exactly 8 times «{8}»
The expression "6$1"
means to return a 6, followed by returning backreference #1 (which was where we
captured the other 8 digits).
For your second issue, to remove the Dollar value, you can use the same UDF but
with different arguments. Although there are other ways to do it, as long as
you have the UDF, you might as well use it.
=TRIM(rerepl(A2,"\$.*?\s"))
Here the expression
\$.*?\s
means:
Match the character “$” literally «\$»
Match any single character that is not a line break character «.*?»
Between zero and unlimited times, as few times as possible, expanding as
needed (lazy) «*?»
Match a single character that is a “whitespace character” (spaces, tabs, line
breaks, etc.) «\s»
So the match is for everything starting with the $ and ending with the next
<space>. We replace it with <nothing>.
Then the TRIM function removes any extra spaces.
============================================
Option Explicit
Function ReRepl(str As String, sPat As String, _
Optional sRepl As String = "") As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.ignorecase = True
re.Pattern = sPat
If re.test(str) = True Then
ReRepl = re.Replace(str, sRepl)
End If
End Function
=====================================
--ron
