Extraction of a number of text from a middle of the cell

[MichalDeu]

Dear All,

I need to extract a number from a cell containing : "Glass unit formula4-12-4-12-4И. R-value - 0,81 m² • С/W"
or similar case. I have a problem with defining in a correct way the left, mid, find etc. Can you help?

 

[MichalDeu]

To be exact I need to extract 0,81

 

[63falcondude]

Please share with us a few examples of strings that you have in your data along with the numbers that you want extracted from those strings.

If we only go off of that one example, the formula might not work for others.

=MID(A1,FIND(",",A1)-1,4) will work for the one example that you shared with us.

 

[MichalDeu]

Thanks a lot. I have a series of strings like : Glass unit formula 4х10(Пл+Ar)х4х10(Пл+Ar)х4ТорN+ R-value - 0,77 m² • С/W - so the same one like showed above.

I also have"Thermal conduction = 0,038 W/mK" should I use the find function twice?

 

[63falcondude]

Thanks a lot. I have a series of strings like : Glass unit formula 4х10(Пл+Ar)х4х10(Пл+Ar)х4ТорN+ R-value - 0,77 m² • С/W - so the same one like showed above.

I also have"Thermal conduction = 0,038 W/mK" should I use the find function twice?

=TRIM(MID(A1,FIND(",",A1)-1,5)) will work for the three examples that you shared with us.

 

[MichalDeu]

=TRIM(MID(A1,FIND(",",A1)-1,5)) will work for the three examples that you shared with us.

Thanks. What about: "Thermic power 21-40kW efficient factor 77,6-77,8%"

btw. slowly I start to understand these functions.

 

[MichalDeu]

oh and I need to extract 77,8

 

[63falcondude]

Thanks. What about: "Thermic power 21-40kW efficient factor 77,6-77,8%"

btw. slowly I start to understand these functions.

I can see this getting out of control soon. Instead of going off of one example at a time, it would be best if you can share several different types of examples with us.

The extraction formulas look for similarities between all of the strings. The formula that I shared in post #5 looks for the first comma and pulls out the 5 characters after the character before that comma.

The string that you shared here is unlike the others. We can make an exception for strings with a "%" symbol, but I have a feeling that there are going to be many more exceptions and the formula will get out of hand rather quickly.

Again, I do not want to go off of your samples one by one and change the formula every time. Please share multiple examples of strings where the formula from post #5 fails and we can go from there.

 

[MichalDeu]

Thank you and Im sorry for the mess:

I need 0,9 from:
Glass unit formula 4М1- 16мм WA, Ar -4 TopN+
R-value - 0,9 m² • С/W

I need 67,7 from:
Thermic power 16,3kW efficient factor 67,7%

I need 98 from:
Thermic power 9-35kW efficient factor <98%