Excel 2003 Find and Replace
 

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?

Excel 2003 Find and Replace
 

Paste a representative sample of your data into a post so's we can have a look.


Gord Dibben MS Excel MVP

On Sun, 2 Dec 2007 12:20:01 -0800, jr0410
wrote:

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?

Excel 2003 Find and Replace
 

On Sun, 2 Dec 2007 12:20:01 -0800, jr0410
wrote:

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?

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