Excel 2003 Find and Replace

  • Thread starter Thread starter jr0410
  • Start date Start date
J

jr0410

I have a column of numbers. Some numbers vary in the number of characters
and some are strickly numbers; others are a mix of numbers and letters. I
would like to replace those numbers with the same numbers but with
parenthesis around each of them. How do I do that?
 
Paste a representative sample of your data into a post so's we can have a look.


Gord Dibben MS Excel MVP
 
I have a column of numbers. Some numbers vary in the number of characters
and some are strickly numbers; others are a mix of numbers and letters. I
would like to replace those numbers with the same numbers but with
parenthesis around each of them. How do I do that?
Click to expand...

If I understand you correctly, you would want the string:

ab12ab33

to change to:

ab(1)(2)ab(3)(3)

This UDF will do that.

To enter the UDF, <alt-F11> opens the VBEditor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code below into the window that opens.

To use this function, enter a formula of the type:

=parennums(A1)

If you want different functionality, it's pretty easy to change this. Post
back.

================================
Option Explicit
Function ParenNums(str As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "(\d)"
ParenNums = re.Replace(str, "($1)")

End Function
==============================================
--ron
 
Back
Top