seems easy but I can't figure it out
I want the last 13 sets of character (as numbers) representing monthly
occurances (the last being the total) of cell values in a1
ZXCVSL 1015L 04 ITEM UNLINED BOX C 0 3 0 2 1 15 2 2 0 0 0 0 24
ZXCVSL 1015L 04 ITEM UNLINED BOX 1 3 0 0 1 1 1 2 0 0 0 0 9
so that for with the 1st line, cell d1 = 0, e1=3, f1=0,g1=2....
and the 2nd line, cell d2 = 1, e2=3, f2=0,g2=0....
You could use a UDF:
<alt-F11> opens the VB Editor.
Ensure your project is highlighted in the project explorer window, then
Insert/Module and paste the code below into the window that opens.
Then use this formula in D1; fill right to P1 and fill down as far as required:
=RegexMid($A1,"\S+",RegexCount($A1,"\S+")-13+COLUMNS($A:A))
=====================================
Option Explicit
Function RegexMid(Str As String, Pattern As String, _
Optional Index As Variant = 1, _
Optional CaseSensitive As Boolean = True, _
Optional MultiLin As Boolean = False) _
As Variant 'Variant as value may be string or array
Dim objRegExp As Object
Dim objMatch As Object
Dim colMatches As Object
Dim i As Long 'counter
Dim T() As String 'container for array results
' Create a regular expression object.
Set objRegExp = CreateObject("vbscript.regexp")
'Set the pattern by using the Pattern property.
objRegExp.Pattern = Pattern
' Set Case Insensitivity.
objRegExp.IgnoreCase = Not CaseSensitive
'Set global applicability.
objRegExp.Global = True
'Set multiline
objRegExp.MultiLine = MultiLin
'Test whether the String can be compared.
If (objRegExp.Test(Str) = True) Then
'Get the matches.
Set colMatches = objRegExp.Execute(Str) ' Execute search.
On Error Resume Next 'return null string if a colmatch index is non-existent
If IsArray(Index) Then
ReDim T(1 To UBound(Index))
For i = 1 To UBound(Index)
T(i) = colMatches(Index(i) - 1)
Next i
RegexMid = T()
Else
RegexMid = CStr(colMatches(Index - 1))
If IsEmpty(RegexMid) Then RegexMid = ""
End If
On Error GoTo 0 'reset error handler
Else
RegexMid = ""
End If
End Function
Function RegexCount(Str As String, Pattern As String, _
Optional CaseSensitive As Boolean = True) As Long
Dim objRegExp As Object
Dim objMatch As Object
Dim colMatches As Object
' Create a regular expression object.
Set objRegExp = CreateObject("vbscript.regexp")
'Set the pattern by using the Pattern property.
objRegExp.Pattern = Pattern
' Set Case Insensitivity.
objRegExp.IgnoreCase = Not CaseSensitive
'Set global applicability.
objRegExp.Global = True
'Test whether the String can be compared.
If (objRegExp.Test(Str) = True) Then
'Get the matches.
Set colMatches = objRegExp.Execute(Str) ' Execute search.
RegexCount = colMatches.Count
Else
RegexCount = 0
End If
End Function
========================================
--ron
