Can I extract numbers from a text string (of varying lengths) and use them?

[kategg]

Hello everyone,

I don't know much about the amazing things excel is capable of but I do know it is bloody clever so I think it is possible to do what I want to do and was wondering if anyone on here knew how to do it. So I have the following information in a column:

2 No FCUs x 6.7 kW
2 No FCUs x 6.7 kW
2 No FCUs x 6.7 kW
4 No FCUs x 6.7 kW
1 No FCU x 3.42 kW
1 No FCU x 3.42 kW

These numbers represent the number of Fan Coil Units in a room and the maximum cooling capacity of each one. In the adjoining column I would like the calculate the total cooling power in the room, so for the first example it would be 2*6.7 kW = 13.4 kW.

Now obviously, that is easy enough to do manually but there are quite a few rows of this type of thing and I have already had to change all the powers once and manually re-calculate the total power in the room. I also know that it would be easy to split this into 2 columns, one with the number of units and one with the power of each unit and work it out easily that way. But someone else set the spreadsheet up as it is in that format and I am curious to know whether it is possible to write a formula that will extract the "No of units" figure and the "power" figure, convert them to numbers and then multiply them to give me my total. That way, any modifications to the column would automatically update the total. From my own research into it it seems possible if you can specify the location of the number you want to extract, but in this case that varies depending on whether there is 1 or more than 1 FCU as if there is more than 1 you have an "s" which adds a character to the text string.

Like I have said, it is not important that I work out how to do this, I am just curious as to whether it is possible and how you would do it if so. So if anyone knows the answer and has the time then it would be much appreciated!

Thanks

[Søren Larsen]

Assuming your data starts in A1, then try this in B1:

=LEFT(A1;1)*MID(A1;FIND("x";A1)+2;FIND(" ";A1;FIND("x";A1)+2)-FIND("x";A1)-2) and copy down. You may have to change ; to , depending on your delimiters.

[Marcol]

It might be better to allow for more than 9 units, try

Please Login or Register  to view this content.

Then you could format the cells
Format cells ... Number Format > Custom
Type:=

Please Login or Register  to view this content.

[Cutter]

How about
=LEFT(A1,FIND(" ",A1)-1)*SUBSTITUTE(MID(A1,FIND("x",A1)+2,50)," kW","")