Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete special character | Excel Discussion (Misc queries) | |||
how to keep the first initial of a name &delete the rest in cell | Excel Discussion (Misc queries) | |||
Addind cell together with a rest. | Excel Discussion (Misc queries) | |||
To select cells containing a certain character ? | Excel Discussion (Misc queries) | |||
delete unused columns and grey out the rest area | Excel Worksheet Functions |