Hi there,
Based on the sample data, it would seem that we could just use the hyphen. As I understood your first post, it appeared that you wished the values 'fixed in place', which this does.
In a Standard Module:
Option Explicit
Sub StripInPlace()
Dim rngLastRow As Range, rngDataToStrip As Range, rngCell As Range
'// Change to suit, Col C = 3, D = 4, etc
Const ColumnNumber As Long = 3
'// Same as above
Const StartingRow As Long = 2
'// Change sheetname to suit//
With ThisWorkbook.Worksheets("MySheet")
Set rngLastRow = RangeFound(Range(.Cells(StartingRow, ColumnNumber), .Cells(Rows.Count, ColumnNumber)))
'// A "safety" in case of an empty sheet.//
If rngLastRow Is Nothing Then Exit Sub
'// Note that although Range appears unqualified, it actually is, //
'// as the start and end of the range are both referenced to //
'// 'MySheet'. //
Set rngDataToStrip = Range(.Cells(StartingRow, ColumnNumber), rngLastRow)
For Each rngCell In rngDataToStrip
If Not InStr(1, rngCell.Value, "-") = 0 Then
'// Basically, Instr is used to get the starting point for Mid, //
'// the return of which is then Trim(med), and converted to //
'// Proper case. //
rngCell.Value = StrConv( Trim( Mid(rngCell.Value, InStr(1, rngCell.Value, "-") + 1) ), vbProperCase )
End If
Next
End With
End Sub
'// See vba help for the Find Method. The below is just using .Find with defaults //
'// to find the last row with data. //
Function RangeFound(SearchRange As Range, _
Optional FindWhat As String = "*", _
Optional StartingAfter As Range, _
Optional LookAtTextOrFormula As XlFindLookIn = xlValues, _
Optional LookAtWholeOrPart As XlLookAt = xlPart, _
Optional SearchRowCol As XlSearchOrder = xlByRows, _
Optional SearchUpDn As XlSearchDirection = xlPrevious, _
Optional bMatchCase As Boolean = False) As Range
If StartingAfter Is Nothing Then
Set StartingAfter = SearchRange(1)
End If
Set RangeFound = SearchRange.Find(What:=FindWhat, _
After:=StartingAfter, _
LookIn:=LookAtTextOrFormula, _
LookAt:=LookAtWholeOrPart, _
SearchOrder:=SearchRowCol, _
SearchDirection:=SearchUpDn, _
MatchCase:=bMatchCase)
End Function
I am 'formula challenged', but if you wanted to return the stripped values in another column, I think this would work.
=PROPER(TRIM(MID(C2,FIND("-",C2)+1,255)))
Hope that helps,
Mark