Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto Number in Invoice | New Users to Excel | |||
extract a phone number from formatting | Excel Discussion (Misc queries) | |||
extract number and use in formula from text & numbers in cell | Excel Worksheet Functions | |||
vlookup with more than number to be retrieved | Excel Worksheet Functions | |||
Extract number in middle of cell | Excel Worksheet Functions |