# Extract a number from a cell containing letters and numbers

#### alexthecamel

##### New Member
Hello all,

I am making a planner for my staff's holiday, and to count all of there sickness lateness etc.

I plan on identifying each with a letter followed by a number for the hours used, eg:

7 Hours overtime would be O7 or O 7
3 Hours Sick would be S3 or S 3

I then want to use a countif to add up all of the numbers in cells containing O and another for all the ones with an S.

I have done this before so i'm sure it can be done, just can't for the life of me remember how.

Any ideas?

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Have you tried:

=COUNTIF(A1:A10,"*O*")

HTH.

Re: Extract a number from a cell containing letters and numb

nearly, it's the numbers after the O that I need to add up. eg:

A1 - O 3
A2 - O 4

Formula should return 7.

Thanks very much anyway.

Re: Extract a number from a cell containing letters and numb

alexthecamel said:
nearly, it's the numbers after the O that I need to add up. eg:

A1 - O 3
A2 - O 4

Formula should return 7.

Thanks very much anyway.
Book7
ABCD
1
2O3O7
3O4S2
46B13
5S2
6B5
7B8
Sheet1

D2:

=SUM(IF(ISNUMBER(--SUBSTITUTE(\$A\$2:\$A\$7,C2,""))*ISNUMBER(SEARCH(C2,\$A\$2:\$A\$7)),--SUBSTITUTE(\$A\$2:\$A\$7,C2,"")))

which is confirmed with control+shift+enter and copied down.

Note that the cost such formulas incur is directly related to the data representation one opts for.

Re: Extract a number from a cell containing letters and numb

Wouldn't it make a lot more sense to organize the data in a table with columns such as Unique ID, Name, Overtime Hours, Sick Hours, etc?

You would then have at your disposal all the tools designed for use with relational databases!
alexthecamel said:
Hello all,

I am making a planner for my staff's holiday, and to count all of there sickness lateness etc.

I plan on identifying each with a letter followed by a number for the hours used, eg:

7 Hours overtime would be O7 or O 7
3 Hours Sick would be S3 or S 3

I then want to use a countif to add up all of the numbers in cells containing O and another for all the ones with an S.

I have done this before so i'm sure it can be done, just can't for the life of me remember how.

Any ideas?

Replies
7
Views
338
Replies
11
Views
361
Replies
6
Views
376
Replies
20
Views
851
Replies
8
Views
344

1,203,353
Messages
6,054,916
Members
444,759
Latest member
TeckTeck

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