Pull Word Before a Certain Word?

[Southerncentralrain]

Is there a formula or way that I can pull a value that is located just before a certain word in each cell? I have sheet that has a lot of information in each cell but I need the BTUs for each item. The cell that has all of the information has the BTUs for each product written like "40,000 BTUs". Do you guys know of a way to pull the value (e.g. 40,000) for each cell?

 

[Brian from Maui]

Try

=SUBSTITUTE(Range,"BTUs",)

If you're using the number for summing etc. use

=--SUBSTITUTE(Range,"BTUs",)

 

[stanleydgromjr]

Southerncentralrain,

Before the macro:

Excel Workbook
	A	B
1	40,000 BTUs
2	140,000 BTUs
3	400 BTUs
4	470,000 BTUs
Sheet1

After the macro:

Excel Workbook
	A	B
1	40,000 BTUs	40000
2	140,000 BTUs	140000
3	400 BTUs	400
4	470,000 BTUs	470000
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).

Press and hold down the 'ALT' key, and press the 'F11' key.

On the 'Insert' menu, click 'Module'.

Copy the below code, and paste it into the Module (on the right pane).

Option Explicit
Sub GetBTUs()
Dim i As Long
Dim rng As Range
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row Step 1
  Set rng = Range("A" & i)
  Cells(i, 2).Value = Abs(GetNums(rng))
Next i
End Sub


Function GetNums(target As Range)
' thomach
' http://www.ozgrid.com/forum/showthread.php?t=63632
'
' =GetNums(A1)
'
    Dim MyStr As String, i As Integer
    MyStr = ""
    If Len(target.Value) = 0 Then GoTo GoExit
    For i = 1 To Len(target.Value)
        If IsNumeric(Mid(target, i, 1)) Then MyStr = MyStr & Mid(target, i, 1)
    Next i
GoExit:
    GetNums = MyStr
End Function

Then run the "GetBTUs" macro.


Have a great day,
Stan

 

[Southerncentralrain]

Awesome. Thanks so much for your time.