Extracting certain numbers from a Cell

[SAFMF]

I am trying to extract the digits after the '-' in this type of data 236-1,46-2,jm007-6,893-2.

Any help would be appreciated.

 

[SAFMF]

Perfect. Thankyou

 

[István Hirsch]

Give this a try too:

=SUM(IFERROR(1*TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,"-",","),",",REPT(" ",99)),{1,3,5,7,9,11,13,15,17}*99,99)),0))

 

[XOR LX]

hi.

Try this shorter version:

=SUMPRODUCT(0+TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,",","-"),"-",REPT(" ",LEN(A1))),LEN(A1)*(2*ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))))-1)+1,LEN(A1))))

Regards

 

[Peter_SSs]

A few comments re the formula suggestions (which are probably preferable to my not-very-robust UDF):

1. Didn't notice it mentioned but the formula solutions suggested, apart from XOR LX's, require entry with Ctrl+Shift+Enter, not just Enter.

2. billszysz: You are replacing any "." characters in the string. I'm guessing the "." in the first post was just signifying the end of a sentence but that replacement could cause unintended consequences. For example, you formula would return 72 for the string "abc-7.2"

3. XOR LX's formula will deal with longer strings than the others. Without knowing the variety of data it's hard to know whether that is useful or not.

4. All the formulas could save one function call since the spaces around the numbers do not stop them being summed. Therefore the TRIM() in each of those formulas is not required.

 

[XOR LX]

A few comments re the formula suggestions (which are probably preferable to my not-very-robust UDF):

And very useful indeed to have one of your comparisons again!

3. XOR LX's formula will deal with longer strings than the others. Without knowing the variety of data it's hard to know whether that is useful or not.

Yes. I was being as "rigorous" as possible, at the "expense" of not having so succinct a formula as e.g. István. It seems to me that in these types of questions we almost always have these two choices to make, i.e.:

Either:

An array formula with IFERROR so that no clause need be inserted to determine the precise array over which to operate

Or:

A non-array version though which does require that extra clause.

Either:

Pick a static, upper bound on the number of returns to be passed to the construction in question, by using e.g. ROW($1:$99) (not very rigorous due to potential row insertions) or an array constant {1,3,5,7,9} (rigorous, though not certain to be sufficient)

Or:

Determine this value dynamically using e.g. ROW(INDIRECT... etc.

4. All the formulas could save one function call since the spaces around the numbers do not stop them being summed. Therefore the TRIM() in each of those formulas is not required.

Good point!

Regards

 

[Peter_SSs]

XOR LX
Interesting points of discussion.

I have ups and downs about rigor here in the forum. It is good to try to cover many circumstances but in trying to do that it is possible we are providing a much more complex and resource-wasting formula than necessary. For example, in this thread we have no idea of the variety of data but it could be that every data item has exactly 4 terms. If that is the case then your formula is really over-the-top. I'm not criticising it though as there may be data with 20 terms - who knows?

Then there is the question about what aspects of the solution might need to be rigorous? Here, the request was to add the digits after the dashes - with one sample. What if a data item might be "abc-x,abc-2"? If this was possible, and the OP wanted the result "2", then the SUM(IFERROR formulas are in fact more rigorous on this point. If the OP wants to know there is a term without digits after the dash then your type of formula will highlight that and the others won't.

Really just saying we do a lot of answering (most of it) without knowing the full facts.

 

[István Hirsch]

Thanks, Peter, you are right: my formula in post #12 is to be confirmed with Ctrl-Shift-Enter (OP!), not just Enter, and the TRIM function is not neccessary.

 

[hiker95]

SAFMF,

Sample raw data:

Excel 2007
	A	B
1	236-1,46-2,jm007-6,893-2
2
Sheet1

After the macro:

Excel 2007
	A	B
1	236-1,46-2,jm007-6,893-2	11
2
Sheet1

Cell Formulas
Range	Formula
B1	=1+2+6+2

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below macro code, and, function
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub SumStrings()
' hiker95, 09/05/2014, ME803498
Dim c As Range, MySum As String, s1, s2, i As Long
Application.ScreenUpdating = False
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  s1 = Split(c, ",")
  MySum = "="
  For i = LBound(s1) To UBound(s1)
    If InStr(s1(i), "-") Then
      s2 = Split(s1(i), "-")
      If IsNumeric(s2(1)) Then
        MySum = MySum & TextNum(s2(1), 0) & "+"
      End If
    End If
  Next i
  If Right(MySum, 1) = "+" Then MySum = Left(MySum, Len(MySum) - 1)
  c.Offset(, 1).Formula = MySum
Next c
Application.ScreenUpdating = True
End Sub
Function TextNum(ByVal txt As String, ByVal ref As Boolean) As String
' jindon
' http://www.mrexcel.com/forum/showthread.php?t=362461
' =TextNum(A1,1)
' 1 for Text only, 0 for Numbers only
With CreateObject("VBScript.RegExp")
  .Pattern = IIf(ref = True, "\d+", "\D+")
  .Global = True
  TextNum = .Replace(txt, "")
End With
End Function

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the SumStrings macro.

 

[XOR LX]

@Peter

I agree on principle. The more we can know, the better we can tailor our solutions accordingly. And you're also right that, without absolute knowledge about the data in question, there's a fine line between rigour and error.

But that's just the problem. In practice, unless we enter into a long, extracted dialogue with the OP as to the exact content and extent of their dataset, not to mention the possibilities for change within those two parameters, we're almost never going to be in a position to know all we can about that dataset.

To a large degree, we're almost always going to have to make some assumptions.

I personally often try for as long as I see realistic to obtain as much information from the OP. And doubly so when it comes to questions regarding string extraction (for which in 99% of cases the initial examples given by the OP don't come anywhere near giving us enough to go on in order to be able to formulate a general, will-work-in-all-cases solution).

In these cases, given that we simply haven't got all the information we need to make that perfect, tailored solution, we have two choices. Either keep chipping away at the OP until we finally do have all that information, or present a solution which, though most likely containing generalised clauses which would not otherwise be necessary if we did have all that information, nevertheless seems to solve the problem in hand.

Caught between a rock and a hard place, is how I'd sum it up (eloquently).

Cheers

 

[hiker95]

XOR LX,

Nicely done. One for my archives - thanks.