Oct 12 2021 03:04 AM
Here I got A5+B1+C11+D2+E5
I want to extract each number after text a b c. and separate it to each cell like C3 to C7
But I don't know which function to use
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:
Select B3.
Enter the formula =SplitText(B2)
If you have an older version:
Select B3:C7.
Enter the formula =SplitText($B$2) and confirm it with Ctrl+Shift+Enter.