# Pull Word Before a Certain Word?

#### Southerncentralrain

##### Board Regular
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?

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

#### Brian from Maui

##### MrExcel MVP
Try

=SUBSTITUTE(Range,"BTUs",)

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

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

#### stanleydgromjr

##### Banned
Southerncentralrain,

Before the macro:

Excel Workbook
AB
140,000 BTUs
2140,000 BTUs
3400 BTUs
4470,000 BTUs
Sheet1

After the macro:

Excel Workbook
AB
140,000 BTUs40000
2140,000 BTUs140000
3400 BTUs400
4470,000 BTUs470000
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).

Code:
``````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
'
' =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

##### Board Regular
Awesome. Thanks so much for your time.

Replies
6
Views
305
Replies
2
Views
305
Replies
0
Views
478
Replies
0
Views
127
Replies
1
Views
182

1,191,685
Messages
5,988,002
Members
440,125
Latest member
vincentchu2369

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back