ExcelBanter - View Single Post - Remove dashes between letters and between letters and digits

Ron Rosenfeld

On Wed, 5 Mar 2008 05:45:19 -0800 (PST), wrote:

Can anyone help me with a script which can remove dashes between 2
letters and between a letter and a digit. But at the same time keeps
the dash beween two digits.

Example:

520-45-3-A-A into 520-45-3AA

I hope I made my example clear!

Mikael

Here is a User Defined Function that should do what you request (retain only
dashes that are surrounded by numbers, if I understand you correctly).

<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 UDF, enter: =RemoveDashes(cell_ref) into some cell.

==========================================
Option Explicit
Function RemoveDashes(str As String) As String
Dim re As Object, mc As Object, m As Object
Set re = CreateObject("vbscript.regexp")
re.IgnoreCase = True
re.Global = True
re.Pattern = "(\d+-?\d+)|[0-9A-Z]"
If re.test(str) = True Then
Set mc = re.Execute(str)
For Each m In mc
RemoveDashes = RemoveDashes & m
Next m
End If
End Function
==================================
--ron