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

  • Thread starter Thread starter Helenf
  • Start date Start date
H

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
 
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".
 
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".
 
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
 
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
 
Hmm! It seems to be interpreting that as 1 PM. Try this formula instead...

=LOOKUP(999,--LEFT(SUBSTITUTE(A1," ",""),ROW($1:$99)))
 
Hmm! It seems to be interpreting that as 1 PM. Try this formula instead...

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