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
>
>
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
>
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,
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]
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