Here's some code samples and a formula example:
Sub ExtractPrefixOnly(sRngAddress As String, lOffsetR As Long, _
lOffsetC As Long, Optional sDelimiter As String = "-")
' Extracts the left side of a delimited string.
' If prefix is numeric then it prepends the result with an apostrophe
' so leading zeros aren't lost.
Dim rng As Range
Dim iPos As Integer
Dim i As Integer
Dim sz As Variant
Set rng = ActiveSheet.Range(sRngAddress)
For i = 1 To rng.Cells.Count
iPos = InStr(1, rng.Cells(i).Text, sDelimiter, vbTextCompare)
If iPos > 0 Then
sz = Left(rng.Cells(i).Text, iPos - 1)
If IsNumeric(sz) Then sz = "'" & sz
rng.Cells(i).Offset(lOffsetR, lOffsetC) = sz
End If
Next
End Sub 'ExtractPrefixOnly()
'In sheet formula: (Column Absolute, Row relative)
'Assumes list is in columnA, target cell is Row1 of target column.
'ColumnB is what I used, but it could be used in any column (or columns).
'Revise to suit and copy where desired (ie: any row or any column[s}
other than source column)
'=IF(NOT(ISERROR(FIND("-",$A1)>0)),LEFT($A1,FIND("-",$A1)-1),"")
'Use example
Sub GetPrefixFromCells()
' Populates target cells according to their Row,Col offset from source cells.
' The source cells are a contiguous selection in ColumnA, though it can be a
single cell.
Const RowOffset As Long = 0 'Stay in the same row
Const ColOffset As Long = 2 'In this case, ColumnC
ExtractPrefixOnly Selection.address, RowOffset, ColOffset
End Sub 'GetPrefixFromCells()
HTH
Kind regards,
Garry
