Excel Formula to Remove Last Word from Cell

I have found both of these formula to work well to removing the last word from a string in a cell in excel.


=LEFT(A1,LOOKUP(2^15,FIND(" "," "&A1,ROW(INDIRECT("1:"&LEN(A1)))))-1)

=LEFT($A2,SEARCH("#",SUBSTITUTE($A2," ","#",LEN($C2)-LEN(SUBSTITUTE($A2," ","")))))

SQL Conversion Query's

Below are some querys that will help in converting data in SQL to a format more friendly with excel.




This can be used to convert a column to varchar(max) which works well with Excel:

Select
convert(varchar(max),convert(ntext,TaskToDo)) as TaskToDo
from t_Task



This is used to replace carriage returns with spaces:

Select
REPLACE(taskitem, Char(13) + Char(10), ' ') as TaskItem
from t_Task



Replace and convert at the same time:

Select 
REPLACE(convert(varchar(max),convert(ntext,TaskToDo)), Char(13) + Char(10), ' ')
from t_Task