Oct 12 2021 03:04 AM
Oct 12 2021 05:09 AM
Copy the following custom function into a module in the Visual Basic Editor:
Function SplitText(s As String) Dim v Dim i As Long Dim j As Long v = Split(s, "+") ReDim a(UBound(v), 1) For i = 0 To UBound(v) For j = 1 To Len(v(i)) If IsNumeric(Mid(v(i), j, 1)) Then Exit For End If Next j a(i, 0) = Left(v(i), j - 1) a(i, 1) = Val(Mid(v(i), j)) Next i SplitText = a End Function
If you have Microsoft 365 or Office 2021:
Enter the formula =SplitText(B2)
If you have an older version:
Enter the formula =SplitText($B$2) and confirm it with Ctrl+Shift+Enter.