First name breakout

I always seem to be asked how to split out part of a name. Just earlier today I was asked how to break out just the first name in a list.

Well you can text to column the list delimited on the space or you can use the following code below to do it.

=LEFT(A2,FIND(" ",A2,1)-1)

Quick and easy. I love these types of problems.


Remove ALT + Enter line Breaks without VBA

I often struggle with data consistency. In my job I am passed multiple spreadsheets from multiple sources every day. I am often asked to bring these into Access.

Well earlier today I received one of these spreadsheets that just makes me want to pull the hair out of my head.

Why so frustrating you ask??? Well in this spreadsheet a user had forced a line break within a cell by pressing the ALT + Enter key. Unfortunately for me, I needed to bring in this data into Access and the ALT + Enter caused me great difficulties.

Adding to my problem was that the spreadsheet was large with many columns and rows of data and in many of the columns there were ALT+Enter's.

Well fortunately I know how to get rid of them and its as easy as 1, 2, 3, 4...

  1. Open the spreadsheet and press CTRL+H to open the find and replace.
  2. In the find what field, hold down the ALT key and type 0010. Now when you do this you wont see anything but trust me and invisible line break character is in the field.
  3. Click in the Replace with field and choose a character to replace them with (such as a space key).
  4. Select Replace All.
So the next time you are stuck with these ALT+Enter line returns, break out your mad Excel skills and show them who's the boss.