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

If it is always in that format and you want to remove the final two
dashes, then this should do it:

=LEFT(A1,LEN(A1)-4)&MID(A1,LEN(A1)-3,1)&RIGHT(A1,1)

Hope this helps.

Pete

On Mar 5, 1:45*pm, 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

Mikael,

Copy the code below into a codemodule of the workbook, then use it like

=FixDashes(A2)

HTH,
Bernie
MS Excel MVP


Function FixDashes(myVal As String) As String
Dim i As Integer
Dim cntr As Integer
FixDashes = myVal
cntr = 2
For i = 2 To Len(myVal) - 1
If Mid(FixDashes, cntr, 1) = "-" Then
If Not (IsNum(Mid(FixDashes, cntr - 1, 1)) _
And IsNum(Mid(FixDashes, cntr + 1, 1))) Then
FixDashes = Left(FixDashes, cntr - 1) & _
Mid(FixDashes, cntr + 1, Len(FixDashes))
Else
cntr = cntr + 1
End If
Else
cntr = cntr + 1
End If
Next i
End Function

Function IsNum(myStr As String) As Boolean
IsNum = False
If Asc(myStr) = 48 And Asc(myStr) <= 57 Then IsNum = True
End Function


wrote in message
...
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

On 5 Mar., 15:06, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
Mikael,

Copy the code below into a codemodule of the workbook, then use it like

=FixDashes(A2)

HTH,
Bernie
MS Excel MVP

Function FixDashes(myVal As String) As String
Dim i As Integer
Dim cntr As Integer
FixDashes = myVal
cntr = 2
For i = 2 To Len(myVal) - 1
* *If Mid(FixDashes, cntr, 1) = "-" Then
* * * If Not (IsNum(Mid(FixDashes, cntr - 1, 1)) _
* * * * * * * And IsNum(Mid(FixDashes, cntr + 1, 1))) Then
* * * * *FixDashes = Left(FixDashes, cntr - 1) & _
* * * * * * * * * * *Mid(FixDashes, cntr + 1, Len(FixDashes))
* * * Else
* * * * *cntr = cntr + 1
* * * End If
* *Else
* * * cntr = cntr + 1
* *End If
Next i
End Function

Function IsNum(myStr As String) As Boolean
IsNum = False
If Asc(myStr) = 48 And Asc(myStr) <= 57 Then IsNum = True
End Function

wrote in message

...



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- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

thank you very much.....

But it keeps telling me that it expects an End Sub????

You shouldn't have any Sub declarations in the codemodule, since both pieces of code that I posted
are User-Defined-Functions.

HTH,
Bernie
MS Excel MVP


wrote in message
...
On 5 Mar., 15:06, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
Mikael,

Copy the code below into a codemodule of the workbook, then use it like

=FixDashes(A2)

HTH,
Bernie
MS Excel MVP

Function FixDashes(myVal As String) As String
Dim i As Integer
Dim cntr As Integer
FixDashes = myVal
cntr = 2
For i = 2 To Len(myVal) - 1
If Mid(FixDashes, cntr, 1) = "-" Then
If Not (IsNum(Mid(FixDashes, cntr - 1, 1)) _
And IsNum(Mid(FixDashes, cntr + 1, 1))) Then
FixDashes = Left(FixDashes, cntr - 1) & _
Mid(FixDashes, cntr + 1, Len(FixDashes))
Else
cntr = cntr + 1
End If
Else
cntr = cntr + 1
End If
Next i
End Function

Function IsNum(myStr As String) As Boolean
IsNum = False
If Asc(myStr) = 48 And Asc(myStr) <= 57 Then IsNum = True
End Function

wrote in message

...



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- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

thank you very much.....

But it keeps telling me that it expects an End Sub????

Try this UDF:

Function dashless(r As Range) As String
Dim ch() As String
'
' gsnuxx
'

v = r.Value
l = Len(v)
ReDim ch(1 To l)

For i = 1 To l
ch(i) = Mid(v, i, 1)
Next

For i = 2 To l - 1
If ch(i) = "-" Then
If IsNumeric(ch(i - 1)) And IsNumeric(ch(i + 1)) Then
Else
ch(i) = ""
End If
End If
Next

For i = 1 To l
dashless = dashless & ch(i)
Next
End Function

--
Gary''s Student - gsnu2007e


" 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

On 5 Mar., 15:31, Gary''s Student
wrote:
Try this UDF:

Function dashless(r As Range) As String
Dim ch() As String
'
' gsnuxx
'

v = r.Value
l = Len(v)
ReDim ch(1 To l)

For i = 1 To l
* * ch(i) = Mid(v, i, 1)
Next

For i = 2 To l - 1
* * If ch(i) = "-" Then
* * * * If IsNumeric(ch(i - 1)) And IsNumeric(ch(i + 1)) Then
* * * * Else
* * * * * * ch(i) = ""
* * * * End If
* * End If
Next

For i = 1 To l
* * dashless = dashless & ch(i)
Next
End Function

--
Gary''s Student - gsnu2007e



" 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- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

I does not give me an error but it does not remove the dash when I
test it???

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