Removing hyphens

[tim williams]

Function formatMe(stuff As String)
stuff = Trim(stuff)
'remove trailing -'s
Do While Right(stuff, 1) = "-"
stuff = Left(stuff, Len(stuff) - 1)
Loop

strTemp = ""
bFlag = False

For n = 1 To Len(stuff)
If Mid(stuff, n, 1) = "-" And bFlag = False Then
strTemp = strTemp & Mid(stuff, n, 1)
bFlag = True
ElseIf Mid(stuff, n, 1) <> "-" Then
strTemp = strTemp & Mid(stuff, n, 1)
bFlag = False
End If
Next n
formatMe = strTemp

End Function

Tim

Mike Van Sydenborgh <poi...@mergetel.com> wrote in message
news:SJku4.22993$bz2.4...@tw12.nn.bcandid.com...
> A file that I import into Excel, leaves hypens around the data I'm
> interested in.
> Some examples are:
>
> DCK-43V---
> FS--4824--G3---
> SWS---7224--S
>
> I want one hyphen between letters like SWS-7224-S and the hypens beyond
> letters to be removed like this: FS-4824-G3
>
> The final product should appear as: DCK-43V, FS-4824-G3,
> SWS-7224-S
>
>

[David McRitchie]

Hi Mike,
This should remove doubled hyphens in place (macro solution),
as well as prefixed and suffixed hyphens for TEXT value
within a selected range. Will ignore numbers like -7.
TRIM which removes spaces will be performed on all text cells
that contain at least one hyphen within the selected area.
Prior to XL97 xlCellTypeConstants might be xlConstants

Option Explicit

Sub REMXHYPS()
'David McRitchie 2000-02-27 excel.programming
'hyphen removals of dups, prefixed, suffixed, and trim results
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual 'pre XL97 xlManual
Dim newstr As String
Dim oldstr As String
Dim lst As String
Dim ix As Integer
Dim x As String
Dim cell As Range
For Each cell In Selection.SpecialCells(xlCellTypeConstants, 2)
'above limits to constants which are TEXT
If InStr(1, cell.Value, "-") Then
lst = "-"
newstr = ""
oldstr = Trim(cell.Value)
For ix = 1 To Len(oldstr)
x = Mid(oldstr, ix, 1)
If x = "-" Then
If lst <> "-" Then newstr = newstr & x
Else
newstr = newstr & x
End If
lst = Mid(oldstr, ix, 1)
Next
If Right(newstr, 1) = "-" Then newstr = Left(newstr, Len(newstr) - 1)
cell.Value = Trim(newstr)
End If
Next
Application.Calculation = xlCalculationAutomatic 'pre XL97 xlAutomatic
Application.ScreenUpdating = True
End Sub

HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://members.aol.com/dmcritchie/excel/excel.htm

tim williams <saxi...@pacbell.net> wrote in message
news:F7mu4.453$Fl3....@news.pacbell.net...

>
> Function formatMe(stuff As String)
> stuff = Trim(stuff)
> 'remove trailing -'s
> Do While Right(stuff, 1) = "-"
> stuff = Left(stuff, Len(stuff) - 1)
> Loop
>
> strTemp = ""
> bFlag = False
>
> For n = 1 To Len(stuff)
> If Mid(stuff, n, 1) = "-" And bFlag = False Then
> strTemp = strTemp & Mid(stuff, n, 1)
> bFlag = True
> ElseIf Mid(stuff, n, 1) <> "-" Then
> strTemp = strTemp & Mid(stuff, n, 1)
> bFlag = False
> End If
> Next n
> formatMe = strTemp
>
> End Function
>

[Mike Van Sydenborgh]

[George Clark]

Here's another variation...it also requires a Selection...

Sub test()
Dim cell As Range, temp As String
For Each cell In Selection
temp = cell
While Right(temp,1) = "-"
temp = Left(temp,Len(temp)-1)
Wend
cell = temp
While InStr(temp, "--") > 0
cell.Replace what:="--", replacement:="-"
temp = cell
Wend
Next
End Sub

George

In article SJku4.22993$bz2.4...@tw12.nn.bcandid.com on 2/27/00 21:05,

[David McRitchie]

Hi Mike, (posted to excel programming)
Code updated to with George Clark's simpler code, but
retaining features I had to restrict selection to text and
not harm negative number values. Also removes hyphens
and spaces from both size not just right as in mentioned
in post. Turning off calculation should dramatically reduce
time. Test to make sure there is a hyphen before starting
reduces chances of messing things up and saves time.

This should remove doubled hyphens in place (macro solution),
as well as prefixed and suffixed hyphens for TEXT value
within a selected range. Will ignore numbers like -7.
TRIM which removes spaces will be performed on all text cells
that contain at least one hyphen within the selected area.
Prior to XL97 xlCellTypeConstants might be xlConstants

Option Explicit
Sub REMXHYPS()
'David McRitchie 2000-02-28

'hyphen removals of dups, prefixed, suffixed, and trim results

'updated based on George Clark's simpler

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual 'pre XL97 xlManual

Dim temp As String

Dim cell As Range
For Each cell In Selection.SpecialCells(xlCellTypeConstants, 2)
'above limits to constants which are TEXT

If InStr(1, cell.Value, "-") Then 'Insure possibility of change
temp = Trim(cell.Value)

While InStr(temp, "--") > 0

temp = Replace(temp, "--", "-")
Wend
If Right(temp, 1) = "-" Then temp = Left(temp, Len(temp) - 1)
If Left(temp, 1) = "-" Then temp = Right(temp, Len(temp) - 1)
cell.Value = Trim(temp)

End If
Next
Application.Calculation = xlCalculationAutomatic 'pre XL97 xlAutomatic
Application.ScreenUpdating = True
End Sub

HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://members.aol.com/dmcritchie/excel/excel.htm

The above code will be added to Rearranging Data in Columns
Location: http://members.aol.com/rexx03/excel/join.htm

> Mike Van Sydenborgh <poi...@mergetel.com> wrote in

> thread http://www.deja.com/=dnc/getdoc.xp?AN=590672177
> > A file that I import into Excel, leaves hyphens around the data I'm

> > interested in.
> > Some examples are:
> >
> > DCK-43V---
> > FS--4824--G3---
> > SWS---7224--S
> >

> > I want one hyphen between letters like SWS-7224-S and the hyphens beyond

> > letters to be removed like this: FS-4824-G3
> >
> > The final product should appear as: DCK-43V, FS-4824-G3,
> > SWS-7224-S

tim williams <saxi...@pacbell.net> wrote in message
[clipped, and noted it looks more like VB code than mine]
George Clark <gac...@sprynet.com> wrote in message
[clipped, and incorporated]

[David McRitchie]

Hi Mike, (XL95 version of previously posted code)
You should have said in your email which version you have,
seems you have XL95 so this variation will work. REPLACE
as VBA code did not work in XL95 as you indicated.

My code was for XL2000 and I think the XL2000 code should
run in XL97 without any changes.

Changes include Calculation, SpecialCells, and Replace
Using Tim's code for the Replace
----------------------------------------------------------------------------
Sub REMXHYPS()
'David McRitchie 2000-02-28 XL95 VERSION

'hyphen removals of dups, prefixed, suffixed, and trim results
'updated based on George Clark's simpler
Application.ScreenUpdating = False

Application.Calculation = xlManual 'pre XL97 xlManual

Dim temp As String
Dim cell As Range

For Each cell In Selection.SpecialCells(xlConstants, 2)

'above limits to constants which are TEXT
If InStr(1, cell.Value, "-") Then 'Insure possibility of change
temp = Trim(cell.Value)

'XL95 version add this
cell.Value = Trim(cell.Value)

While InStr(temp, "--") > 0
'temp = Replace(temp, "--", "-")

'XL95 version of the above...

cell.Replace what:="--", replacement:="-"

temp = cell.Value

Wend
If Right(temp, 1) = "-" Then temp = Left(temp, Len(temp) - 1)
If Left(temp, 1) = "-" Then temp = Right(temp, Len(temp) - 1)
cell.Value = Trim(temp)
End If
Next

Application.Calculation = xlAutomatic 'pre XL97 xlAutomatic

Application.ScreenUpdating = True
End Sub

-------------------------------------------------------------------------------
Will be Documented in http://members.aol.com/rexx03/excel/join.htm
HTH, David McRitchie, Microsoft MVP, Excel