Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find and Replace - Changing 1st # only
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find and Replace - Changing 1st # only
You could use VBA for this.
Sub test() For Each cell In Range("C1:C50") If Left(cell.Value, 1) = 7 _ Then cell.Value = 6 & Right(cell.Value, 8) Else End If Next cell End Sub Adjust your range as necessary. Regards, Paul -- "Studebaker" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find and Replace - Changing 1st # only
On Oct 11, 6:16 pm, Studebaker
wrote: 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 In a helper column: ="6" & RIGHT(D10,LEN(D10)-1) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find and Replace - Changing 1st # only
On Thu, 11 Oct 2007 15:16:01 -0700, Studebaker
wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find and replace - problem with automatically changing formatting | Excel Discussion (Misc queries) | |||
Find and Replace without changing font | Excel Discussion (Misc queries) | |||
find and replace - replace data in rows to separated by commas | Excel Worksheet Functions | |||
changing formulas to values so that they will be recognized by Find and Replace | Excel Discussion (Misc queries) | |||
Excel-how (find/replace) for multifmat cells w/o changing fmat | Excel Discussion (Misc queries) |