extract number
 

I have a work sheet that has a number of rows that contain a drop down list.
This list is populated by a number of different criteria. Here's one
example: "Install / Sand and Finish 2 1/4" x 3/4" #1 Red Oak in Living room.
(259.88 sq ft)"
I would like to be able to get a total sq ft of each row that is an
"install". There are times when there would be multiple "installs" and I
need to total these together.
Thanks for the Help

extract number
 

Put this in a helper column and copy down.........then sum the helper column

=IF(LEFT(A1,7)="install",MID(A1,FIND("(",A1,1)+1,L EN(A1)-FIND("
",A1,FIND("(",A1,1)))*1,"")

All one line, watch out for word-wrap

Vaya con Dios,
Chuck, CABGx3



"smonsmo" wrote:

I have a work sheet that has a number of rows that contain a drop down list.
This list is populated by a number of different criteria. Here's one
example: "Install / Sand and Finish 2 1/4" x 3/4" #1 Red Oak in Living room.
(259.88 sq ft)"
I would like to be able to get a total sq ft of each row that is an
"install". There are times when there would be multiple "installs" and I
need to total these together.
Thanks for the Help

extract number
 

To extract the sq ft value form the text would take some doing, especially
as you say there could be more than one.
But you say these all come form a list so there is an alternative method.
Say the list is in a1:A50 of Sheet3
It would be a small task to manually enter in the corresponding B cells the
sq footage of each project
Returning to the main sheet with the rows of entries - let's say the first
on is in A1
Then in B1 enter =INDEX(Sheet3!B1:B5,MATCH(A1,Sheet3!A1:A5))
This will return the sq footage of the project
Not sure why I went this route when VLOOKUP would also do!
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"smonsmo" wrote in message
...
I have a work sheet that has a number of rows that contain a drop down
list.
This list is populated by a number of different criteria. Here's one
example: "Install / Sand and Finish 2 1/4" x 3/4" #1 Red Oak in Living
room.
(259.88 sq ft)"
I would like to be able to get a total sq ft of each row that is an
"install". There are times when there would be multiple "installs" and I
need to total these together.
Thanks for the Help

extract number
 

On Tue, 23 Jan 2007 10:08:01 -0800, smonsmo
wrote:

I have a work sheet that has a number of rows that contain a drop down list.
This list is populated by a number of different criteria. Here's one
example: "Install / Sand and Finish 2 1/4" x 3/4" #1 Red Oak in Living room.
(259.88 sq ft)"
I would like to be able to get a total sq ft of each row that is an
"install". There are times when there would be multiple "installs" and I
need to total these together.
Thanks for the Help

Is the square footage reliably identified by being a number followed by " sq."?

You could download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr


and then use this formula:

=--REGEX.SUBSTITUTE(A1,".*Install.*?(\d*\.?\d+) sq.*","[1]",,,FALSE)


--ron

extract number
 

The "sq ft" is include at the end of each line. Some of these would be a
sand and finish and I would not want to include these in my total.

"Ron Rosenfeld" wrote:

On Tue, 23 Jan 2007 10:08:01 -0800, smonsmo
wrote:

I have a work sheet that has a number of rows that contain a drop down list.
This list is populated by a number of different criteria. Here's one
example: "Install / Sand and Finish 2 1/4" x 3/4" #1 Red Oak in Living room.
(259.88 sq ft)"
I would like to be able to get a total sq ft of each row that is an
"install". There are times when there would be multiple "installs" and I
need to total these together.
Thanks for the Help

Is the square footage reliably identified by being a number followed by " sq."?

You could download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr


and then use this formula:

=--REGEX.SUBSTITUTE(A1,".*Install.*?(\d*\.?\d+) sq.*","[1]",,,FALSE)


--ron

extract number
 

On Tue, 23 Jan 2007 19:46:00 -0800, smonsmo
wrote:

The "sq ft" is include at the end of each line. Some of these would be a
sand and finish and I would not want to include these in my total.

Then the method I posted should work for you.

Let me know.


--ron