how to select the first character in a cell and delete the rest

[Helenf]

Hi and thank you in advance
I have a large spreadsheet where they have populated values like 3x3ml,
5x0.5ml etc. They are all pretty different. We only need the first numbers
before the x. How do I do this? Please help

 

[Mike H]

Hi,

Try this

=LEFT(A1,SEARCH("x",A1)-1)+0

Mike

 

[Rick Rothstein]

Here is another way to get the leading number, assuming the leading numbers
are less than 1000 (increase the 999 if the numbers can be bigger)...

=LOOKUP(999,--LEFT(A1,ROW($1:$99)))

This method will return the leading number even if the character after it is
not an "x".

 

[Helenf]

Thank you this helped! I noticed further down the spreadsheet that not
everything was 12 x some were 200 pieces etc so this helped great. However
for some reason it returns 1 PACK as 0.542. But thats a simple replace all.
Thanks again

 

[Rick Rothstein]

Hmm! It seems to be interpreting that as 1 PM. Try this formula instead...

=LOOKUP(999,--LEFT(SUBSTITUTE(A1," ",""),ROW($1:$99)))