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

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

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

Hi,

Try this

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

Mike

"Helenf" wrote:

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

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

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".

--
Rick (MVP - Excel)


"Helenf" wrote in message
...
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

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

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" wrote:

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".

--
Rick (MVP - Excel)


"Helenf" wrote in message
...
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

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

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

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

--
Rick (MVP - Excel)


"Helenf" wrote in message
...
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" wrote:

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".

--
Rick (MVP - Excel)


"Helenf" wrote in message
...
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