What can I say... This formula will produce #VALUE! only if the cell is
empty or does not start with a number. Are you sure you are using it
properly? In my formula, replace A2 with whatever cell reference
contains the number/text (2 replacements).

If you are guaranteed that there will be a space after the number, you
can try this simpler formula instead:

=LEFT(A2, FIND(" ",A2)-1)

This formula will also produce #VALUE! if no space is found in the text
or in any of the above conditions. This one is NOT an array formula
(simply press Enter). If used properly, the first version is more
powerful (allows for no space).

Does this help?

Kostis