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

Conga Mail Merge - Display All Merge Fields // Insert Merge Field Shortcut

There are quite a number of little quirks that are nice to know when using the Salesforce.com AppExchange app Conga Composer to create merge documents. While calling their support line is very helpful, sometime I feel like I need to give them a break.

Here is a function I use all the time..

Display all Merge Fields in Word:
ALT + F9

Insert Merge Field
ATL + I + F + M + M

Migrate Reports from Salesforce Production to Production Using Ecplise

When moving from Sandbox to production you have the option of using change sets to quickly move reports, objects, triggers, classes ect. to a production enviornment. I was posed the problem of moving a number of reports from one Production Org to another Production Org that would have the same fields and same configuration.

With production org's not able to connect to other production org's via change sets I had to get creative, here is what I did.


CAUTION: When handling metadata components from a production enviornment in eclipse you must be VERY careful as you can screw things up in a hurry! I suggest playing around with this in a developer org before doing anything in production.

1. Connect Eclipse to the production environment of both org's.
2. When downloading a project in Eclipse, be sure to select all metadata components.
3. One of your folders will be Reports.
4. Open up the report you want to recreate. It will open a window, at the bottom of the page change your option from 'Design' to 'Source'
5. In the instance you are going to migrating the report right click on the report folder where you want the report to appear, hover over 'New' and click the 'Other..' option.
6. Under the 'XML' folder click 'XML' then 'Next'
7. You can give the report the same name as the one being copied, so 'SampleReport.report' would work.
8. Next you need to copy the XML code from step 4 and paste it into this new XML file you have created. 
9. NOTE: if the fields that are in the report are not consistent between the org's you will get errors
10. Finally click save, then right click on the report folder and select 'Force.com' > 'Save to Server' and that's it!

Adding a Carriage Return in Excel

To add a carriage return to a cell in excel it is as simple as ALT + Return.

In order to use  a carriage return in a formula simply use char(10) like this.

=A1&char(10)&B1

or if you want multiple returns just keep adding char(10)

=A1&char(10)&char(10)&char(10)&char(10)&B1

NOTE: IF this is not working you may need to format the cells you are trying to do this with to 'Wrap Text'

Limit Results in SQL Server Using Row_Number

Here is a sample query that limits the number of results in a query to 500. The first bold item is the column the results need to be ordered by, and the second bold item is the table that your are pulling the data from. Everything else can be used as is to pull all columns from a table and limit the results to 500 rows.


SELECT * FROM
( SELECT
    ROW_NUMBER() OVER (ORDER BY ikeyid ASC) AS ROW_NUMBER,
    *
  FROM A_Table
) foo
WHERE ROW_NUMBER <= 500


You can also adjust the where statement in order to grab a range of records. I had to do this in order to pull out chunks of a huge table rather than pulling them all at once.

WHERE ROW_NUMBER > 0 AND ROW_NUMBER <= 50000

WHERE ROW_NUMBER > 50000 AND ROW_NUMBER <= 100000

WHERE ROW_NUMBER > 100000

Running this query 3 times with the 3 different where statements listed above will result in 3 results sets that have 50,000 rows a piece and the final query pulls in any row over the first 100,000.

Truncating Text In a Trigger Using APEX

Recently I was asked to put a trigger together that copied a Long Text Area value that can hold up to 32,000 characters to a Text Area field that can only hold up to 255 characters. Naturally when you try to copy a value larger than 255 characters to a Text Area field an error will be thrown. So I had to figure out how to basically truncate a string value to 255 characters in order to assign it to a Text Area field.

The string method 'substring' does the trick! What is does is much like in excel using the left(), mid() and right() formulas you give it a starting place and tell it how many characters to grab. Salesforce's basic example is as follow:

'hamburger'.substring(4, 8); 
// Returns "urge" 
    

'smiles'.substring(1, 5);
// Returns "mile" 

My problem was a bit more complex as I had to include logic that would say only perform this truncation when the Long Text Area value is larger than 255 characters. If you try to truncate a string value using substring to a length longer than the string itself you will get an error. Here is the code I used for this instance.

   string EX1 = Sample.LongTextArea1__c;
   string EX2 = Sample.LongTextArea2__c;
   
   //Get sizes of Goal and Strategy in the event they are longer than 255 char and need to be truncated
   Integer size1 = EX1.length();
   Integer size2 = EX2.length();
   
   //If Goal or Strategy are longer than 255 characters they need to be truncated
   if(size1> 255){
         EX1= EX1.substring(0, 255);
   }
   if(size2 > 255){
         EX2 = EX2.substring(0, 255);
   }

I retrieve the length of the strings that were assigned the value of the Long Text Area (EX1 and EX2) and if those lengths are more than 255 characters then I truncated it with the substring method. If they are shorter than the maximum length of a Text Area (255) I do not adjust them at all.

Below is a link to the String Methods as defined by Salesforce.com.

http://www.salesforce.com/us/developer/docs/apexcode/index.htm