# Search for text and extract number

#### thealley

##### New Member
Hi,

I have a range of cells, say, A1 to P100. I would like to search for names plus adjacent number of hours in this range of cells (example cell A1 is Joe 5hrs, A2 is Diane 6hrs, C5 is Joe 3hrs, D8 is Chris 2hrs.

So then, I would like, example columns S1 to S3 to be: 8, 6, 2, representing the total number of hours of each person.

If I add another entry in E5 as Diane 3hrs, column S updates to 8, 9, 2. And so on.

I looked into a mixture of COUNTIF, ISNUMBER and SEARCH, but it was getting too complex for me.

Any help would be appreciated.

Thanks

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

##### Well-known Member
HI
As a start
Try this code covers A1:E100

VBA Code:
``````Sub test()
Dim a As Variant
Dim e, x
a = Range("A1:E100")
With CreateObject("scripting.dictionary")
For Each e In a
If e <> "" Then
x = Split(e)
If Not .exists(x(0)) Then
.Add x(0), Split(x(1), "hrs")(0) * 1
Else
.Item(x(0)) = .Item(x(0)) + Split(x(1), "hrs")(0) * 1
End If
End If
Next
Range("S1").Resize(.Count) = Application.Transpose(.items)
Range("T1").Resize(.Count) = Application.Transpose(.keys)
End With
End Sub``````

#### thealley

##### New Member

Thanks for your reply. I'll try this out at home to get a hands on.

Actually I want this for work, and we're not allowed to use VBA codes, so I was looking more into some formulas (if it can be done that way). Apologies for not advising beforehand.

Thanks.

Replies
1
Views
219
Replies
2
Views
73
Replies
0
Views
57
Replies
0
Views
109
Replies
8
Views
120

1,130,015
Messages
5,639,558
Members
417,098
Latest member
steverob

### 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.

### Which adblocker are you using?

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

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