Use of Wildcard characters with replace
Is it possible to use wildcard characters in the replace string and have them
be treated as wildcards instead of text. I have a large spreadsheet that contains a column of phone numbers. These phone numbers have all been entered by different people and I want to normalize the way they are presented. Some have been entered as [1] 555-555-1234, and I'd like to find all instances like this and replace it with (555) 555-1234. I can use wildcards to find all of these instances (find "[1] ???-"), but I can't replace using the wildcard character (replace "(???)" doesn't work. I also have instances of both (555) 555-1234 and (555)555-1234. I'd like to search on all the items that have no space after the parentheses and insert a space. Can anyone help with this? |
Use of Wildcard characters with replace
Two step.
1. EditReplace What: [?] With: nothing Replace all. 2. Add this UDF to a workbook general module. Function RemAlphas(ByVal sStr As String) As Long Dim i As Long If sStr Like "*[0-9]*" Then For i = 1 To Len(sStr) If Mid(sStr, i, 1) Like "[0-9]" Then RemAlphas = RemAlphas & Mid(sStr, i, 1) End If Next i Else RemAlphas = sStr End If End Function =RemAlphas(cellref)*1 copy down to return 5555551234 FormatCellsSpecialPhone Number Gord Dibben MS Excel MVP On Fri, 6 Nov 2009 11:21:01 -0800, tonuab wrote: Is it possible to use wildcard characters in the replace string and have them be treated as wildcards instead of text. I have a large spreadsheet that contains a column of phone numbers. These phone numbers have all been entered by different people and I want to normalize the way they are presented. Some have been entered as [1] 555-555-1234, and I'd like to find all instances like this and replace it with (555) 555-1234. I can use wildcards to find all of these instances (find "[1] ???-"), but I can't replace using the wildcard character (replace "(???)" doesn't work. I also have instances of both (555) 555-1234 and (555)555-1234. I'd like to search on all the items that have no space after the parentheses and insert a space. Can anyone help with this? |
Use of Wildcard characters with replace
After much research, I have determined that it is just not possible to
combine wildcard characters with replace. They are very useful for searching, but they don't help with the situation I detailed above. For anyone that might be interested in how I resolved my issue, I basically removed all non-numeric characters from my data, so that each of my phone number cells was formatted as 5555551234. Then, I converted the text to tables, using fixed width columns of 3 characters, 3 characters and four characters. Then, in a fourth column, I entered the formula =CONCATENATE("(",A1,") ",A2,"-",A3). I then copied and pasted the values of this column io ntthe original column to replace it with the updated formatting. It was a bit of pain because I had to copy the formula into each cell in a very long column (over 17,000 fields) but copying and pasting groups of cells made this a very quick fix. I'm sure there is a much neater way to do this programmatically, but my skill level is very limited in those areas. |
All times are GMT +1. The time now is 07:47 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com