A Few More Excel Tricks (Cell Search, Concatenation, Text to Column)

Just used a couple of helpful excel tricks that it would be nice if they were documented.

The first problem was that I had a document I was trying to import as an attachment and in a very random way about 20% of the path names had the text "~~SYNCSTAMP=1236547" where the numbers were completely random every time. So I wanted to tag every instance that had just "~~SYNCSTAMP=", here is the formula I used to search for this string. I had to do this because with random numbers the find and replace function would not work.

 =IF(ISNUMBER(SEARCH("~~SYNCSTAMP=",E2)),"x","")

Where the specified text appears in the cell it is marked with an 'x' and if it does not appear then it is left blank. Next I just sorted the column to only have the ones with the SYNCESTAMP at the top.

An easier use of this formula to search for just '@' would be:

         =IF(ISNUMBER(SEARCH("@",E2)),"x","")

____________________________________________________________________________

Concatenates the ending off of the cell.

=left(E2,len(E2)-26)

Where the 26 was the total number of characters I wanted to chop off at the end.


____________________________________________________________________________

Text to Columns with Carriage Returns:

When breaking things up from a single cell using Text to Columns you can specify that to be on Carriage Returns (ALT + Enter) in a cell. Use the 'Other' option and while in the text box use CTRL + J and nothing will appear but that will break the cells out that were on separate lines to separate cells.