Extract a number from a cell containing letters and numbers

[alexthecamel]

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?

 

[martinee]

Have you tried:

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

HTH.

 

[alexthecamel]

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.

 

[Aladin Akyurek]

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.

Book7
	A	B	C	D
1
2	O3		O	7
3	O4		S	2
4	6		B	13
5	S2
6	B5
7	B8
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.

 

[tusharm]

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!

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?