Subtract 2 numbers from cell with text

[Alphacsulb]

I'm not sure if this is possible but I have a cell with the following:

Sample Ballot Pamphlets / 30.5 of 32 pages /

Is it possible to make a formula that says, look for the 2 numbers in the cell and subtract the 1st number from the second.

Result = 1.5

I know it's a long shot but figured it might be possible.

 

[T. Valko]

I'm not sure if this is possible but I have a cell with the following:

Sample Ballot Pamphlets / 30.5 of 32 pages /

Is it possible to make a formula that says, look for the 2 numbers in the cell and subtract the 1st number from the second.

Result = 1.5

I know it's a long shot but figured it might be possible.

Will the text always be the same except for the numbers?

Sample Ballot Pamphlets / 30.5 of 32 pages /
Sample Ballot Pamphlets / 300 of 2000 pages /
Sample Ballot Pamphlets / 10 of 10 pages /

 

[Alphacsulb]

Correct, they are always the same.

 

[Boller]

One possibility (a bit klunky, though) is to do Text to Columns then :-

<TABLE style="WIDTH: 110pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=147><COLGROUP><COL style="WIDTH: 110pt; mso-width-source: userset; mso-width-alt: 5376" width=147><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 110pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" id=td_post_2700009 class=xl65 height=17 width=147>=MAX(A1:I1)-MIN(A1:I1)</TD></TR></TBODY></TABLE>

 

[Alphacsulb]

Text to Columns wont work because I could have multiple lines like the example given:

Sample Ballot Pamphlets / 30.5 of 32 pages /
Sample Ballot Pamphlets / 300 of 2000 pages /
Sample Ballot Pamphlets / 10 of 10 pages /

Helper columns can be used.

 

[T. Valko]

Correct, they are always the same.

It's not exactly elegant, but it works...

Book1
	A	B
2	Sample Ballot Pamphlets / 30.5 of 32 pages /	1.5
3	Sample Ballot Pamphlets / 300 of 2000 pages /	1700
4	Sample Ballot Pamphlets / 10 of 10 pages /	0
Sheet1

Formula entered in B2:

=MID(A2,SEARCH("of",A2)+3,SEARCH(" page",A2)-SEARCH("of",A2)-3)-MID(A2,SEARCH("/",A2)+2,SEARCH(" of",A2)-SEARCH("/",A2)-2)

 

[Alphacsulb]

It works when I put it into my a new spreadsheet, but it doesn't work in my existing document. The result is always 0. All of my cells have a format of General, any ideas on why? I had the same problem with a different formula but couldn't find out the answer.

 

[Dryver14]

when I copied your dilema into a spreadsheet it was merged and centred and Biff's formula did not work when I removed merge and centre from the cell it worked fine, its how you have the original page formatted

 

[hiker95]

Alphacsulb,

If you were looking for a macro solution:


Sample data before the macro:

Excel Workbook
	A	B
1	Sample Ballot Pamphlets / 30.5 of 32 pages /
2	Sample Ballot Pamphlets / 300 of 2000 pages /
3	Sample Ballot Pamphlets / 10 of 10 pages /
4
Sheet1

After the macro:

Excel Workbook
	A	B
1	Sample Ballot Pamphlets / 30.5 of 32 pages /	1.5
2	Sample Ballot Pamphlets / 300 of 2000 pages /	1700
3	Sample Ballot Pamphlets / 10 of 10 pages /	0
4
Sheet1

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 code, by highlighting the code and pressing the keys CTRL + C
2. Open your 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 by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub Subtract2()
' hiker95, 04/28/2011
' http://www.mrexcel.com/forum/showthread.php?t=546805
Dim c As Range, H As String, Sp, a As Double, b As Double
Application.ScreenUpdating = False
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  H = Mid(c, Application.Find("/", c, 1) + 2, Application.Find("pages", c, 1) - 2 - Application.Find("/", c, 1) - 1)
  Sp = Split(H, " of ")
  c.Offset(, 1).Value = CDbl(Sp(1)) - CDbl(Sp(0))
Next c
Application.ScreenUpdating = True
End Sub

Then run the Subtract2 macro.

 

[T. Valko]

It works when I put it into my a new spreadsheet, but it doesn't work in my existing document. The result is always 0. All of my cells have a format of General, any ideas on why? I had the same problem with a different formula but couldn't find out the answer.

It would help if you could post a sample file with data that this formula does not work on.

You can use a free file hosting site if need be.