=LEFT Function

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

Using the function below will allow me to find the 1st character if
separated by a period.

=LEFT(A1,FIND(".",A1)-1)

For instance, it will provide me the following:
1.1 becomes 1
2.1.1 becomes 2

However, if I may have also a single digit that needs to become itself in
the function.
1.1 becomes 1
2.1.1 becomes 2 AND
3 becomes 3

How do I modify the "=LEFT(A1,FIND(".",A1)-1)" to pick up all the 3 previous
listed values (1, 2, 3)?

Thanks,
Tom
 
Hi

Using your existing formula will pull in all of the digits before the first
.. If there isn't a . (eg 3 ) then it will give you an error
 
=IF(ISNUMBER(FIND(".",A1)),--LEFT(A1,FIND(".",A1)-1),--A1)

that should work; it converts all numbers to values also. If you have mix of
alpha and numbers, remove the --s.
 
Thanks, Dave.

This works great, too.

Tom


Dave R. said:
=IF(ISNUMBER(FIND(".",A1)),--LEFT(A1,FIND(".",A1)-1),--A1)

that should work; it converts all numbers to values also. If you have mix of
alpha and numbers, remove the --s.
Click to expand...
 
Andy,

I know... that's why I posted this question. Please refer to Frank's or
Dave's response for a working solution.

Tom
 
Back
Top