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.

 

[Peter_SSs]

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.

Welcome to the MrExcel board!

Is that blue text in one cell or multiple?
If multiple, what is in what cell?
Please explain the result(s) you expect from that data and where the result(s) will be.

 

[Momentman]

WELCOME TO THE FORUM

So whats your expected result for the sample one above

 

[SAFMF]

No it is not blue text just standard. What I am looking to do is extract the 2,6 & 2 after the '-' so that I can add these together

 

[Peter_SSs]

No it is not blue text just standard.

What does that mean, it is still not clear. Remember, we cannot see your worksheet. If you don't show us then you have to tell us exactly what is in each cell.

 

[SAFMF]

Basically ignore that the text is blue on the post as i just copied it from the excel sheet. I have an export from our company website that returns results in a raw format as above, the numbers that I am interested in are the ones that following a '-' so in the example given 1,2,6 & 2, i then need to combine these to give me a total of 11.

Hope that helps?

 

[XOR LX]

But you still haven't clarified whether the data you gave is in a single cell or several.

Regards

 

[SAFMF]

It is currently in a single cell

 

[Peter_SSs]

It is currently in a single cell

At last, thank you.

BTW, the blue text I was referring to was in my post, not yours.


You might consider this user-defined function and test it in a copy of your workbook.
To implement ..

1. Right click the sheet name tab and choose "View Code".

2. In the Visual Basic window use the menu to Insert|Module

3. Copy and Paste the code below into the main right hand pane that opens at step 2.

4. Close the Visual Basic window.

5. Enter the formula as shown in the screen shot below and copy down.

6. If using Excel 2007 or later your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function DashSum(s As String) As Single
  Dim Bits
  Dim i As Long
  
  Bits = Split(s, ",")
  For i = 0 To UBound(Bits)
    DashSum = DashSum + Split(Bits(i), "-")(1)
  Next i
End Function

Excel Workbook
	A	B
1
2	236-1,46-2,jm007-6,893-2	11
3	236-865	865
4	fff-0,46-9	9
Sheet1

 

[billszysz]

Brute force method
=SUM(IFERROR(--TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,".",""),","," "),"-"," ")," ",REPT(" ",LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,".",""),","," "),"-"," ")))),{1,3,5,7}*LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,".",""),","," "),"-"," "))+1,LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,".",""),","," "),"-"," ")))),""))

If you have more than 4 numbers in your cells you can change this part of formula
{1,3,5,7} to {1,3,5,7,9} for 5 numbers
{1,3,5,7} to {1,3,5,7,9,11} for 6 numbers etc.

I think exist rather a more elegant solution