Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default 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


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,441
Default 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



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default 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????
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,058
Default 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




  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default 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???
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,441
Default 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????


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How can I remove a space between a letters and set of numbers? connie Excel Discussion (Misc queries) 5 July 2nd 07 04:31 PM
how to change small letters to capital letters HOW TO CHANGE Excel Discussion (Misc queries) 4 May 30th 07 01:12 AM
can't enter in excel some letters, or digits. Darius Excel Discussion (Misc queries) 1 February 2nd 07 09:40 PM
How do change a column of data in capitol letters to small letters Barb P. Excel Discussion (Misc queries) 6 November 15th 06 07:17 PM
change lower letters to upper letters Winnie Excel Discussion (Misc queries) 2 September 15th 06 04:58 AM


All times are GMT +1. The time now is 07:30 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"