Remove dashes between letters and between letters and digits
 

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

Remove dashes between letters and between letters and digits
 

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

Remove dashes between letters and between letters and digits
 

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

Remove dashes between letters and between letters and digits
 

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????

Remove dashes between letters and between letters and digits
 

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

Remove dashes between letters and between letters and digits
 

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

Remove dashes between letters and between letters and digits
 

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???

Remove dashes between letters and between letters and digits
 

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????