Extracting certain numbers from a Cell

Peter_SSs

MrExcel MVP, Moderator

Joined

May 28, 2005

Messages

63,880

Office Version

1. 365

Platform

1. Windows

• Sep 5, 2014

• #2

SAFMF said:

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.

Click to expand...

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.

 

Upvote 0

Momentman

Well-known Member

Joined

Jan 11, 2012

Messages

4,153

Office Version

1. 365

Platform

1. Windows
2. MacOS

• Sep 5, 2014

• #3

WELCOME TO THE FORUM

So whats your expected result for the sample one above

 

Upvote 0

S

SAFMF

New Member

Joined

Sep 5, 2014

Messages

13

• Sep 5, 2014

• #4

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

 

Upvote 0

Peter_SSs

MrExcel MVP, Moderator

Joined

May 28, 2005

Messages

63,880

Office Version

1. 365

Platform

1. Windows

• Sep 5, 2014

• #5

SAFMF said:

No it is not blue text just standard.

Click to expand...

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.

 

Upvote 0

S

SAFMF

New Member

Joined

Sep 5, 2014

Messages

13

• Sep 5, 2014

• #6

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?

 

Upvote 0

XOR LX

Well-known Member

Joined

Jul 2, 2012

Messages

4,517

• Sep 5, 2014

• #7

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

Regards

 

Upvote 0

S

SAFMF

New Member

Joined

Sep 5, 2014

Messages

13

• Sep 5, 2014

• #8

It is currently in a single cell

 

Upvote 0

Peter_SSs

MrExcel MVP, Moderator

Joined

May 28, 2005

Messages

63,880

Office Version

1. 365

Platform

1. Windows

• Sep 5, 2014

• #9

SAFMF said:

It is currently in a single cell

Click to expand...

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)

Rich (BB code):

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

 

Upvote 0

B

billszysz

Active Member

Joined

Feb 26, 2014

Messages

384

Office Version

1. 365

• Sep 5, 2014

• #10

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

 

Upvote 0