Forum Discussion
How to sum the numbers in a text string?
Mr. Chen
copy the function below in a module then call it in the cell you preferred.
example:
A1 = Path 8.5
in cell B1 copy the formula =Digits(A1)*1
B1 will show 8.5
from there you can now do what you want.
** try this in a test workbook -- always make back-up copy of your work.
HTH
Function Digits(ByVal S As String) As String 'courtesy of Rick Rothstein
Dim X As Long
For X = 1 To Len(S)
If Mid(S, X, 1) Like "[!0-9,.]" Then Mid(S, X) = Chr(1)
Next
Digits = Trim(Replace(S, Chr(1), ""))
End Function
Thanks!
Is there any possibility to extract the multi digits of a text string and return the sum of these digits in one cell?
Say the text string "Path 8.5+Metrocard 10" is in one cell, and I have thousand cells with similar description, that I can't manually separate the text string into multi string like "Path 8.5" and "Metrocard 10" and use the digit function.
Might need a way to extract the items in the text first? or there is a sophisticated way to do it in one step?
Thanks again
