Copying from one sheet to another

What your formula is doing is extracting a sub-string starting after the first "space" character and ending at the first comma encountered. This matches with the result you actually see.

I can't intuit what your formula is meant to do as opposed to what it is actually doing.

You need to explain your intent and/or provide more than the one example you have so far provided.

Hi Geoff,

I just want to extract this word "in 900 mL 0.01N HCl, 700mL (2hrs)→ Phosphate Buffer pH6.8 with SLS (0.5%)"
from the in 900 mL 0.01N HCl, 700mL (2hrs)→ Phosphate Buffer pH6.8 with SLS (0.5%)Paddle, 50rpm, 37°C by HPLC Analysis

Thank you

.
This formula will give you what you are seeking :

=LEFT(A1,73)

But, if anything in the string changes (position or length of the string), the formula will most likely not provide what you are seeking.

Example: if the first word "in" is no longer present at the beginning of the string,the formula as written will give you :

900 mL 0.01N HCl, 700mL (2hrs)→ Phosphate Buffer pH6.8 with SLS (0.5%)Pa Note there is a space at the beginning of the line and
now there is also the "Pa" at the end.