Pat Egen’s Weblog

Ramblings, chats, thoughts, ideas

Once again, Excel to the rescue

leave a comment »

Yesterday, I got an interesting request for an Excel formula. This person wanted to take the first word in a string, and add it to the end of the string, preceded by a comma. In other words, The cat in the hat would become cat in the hat, The.Patricia Egen Consulting FAST Tips

Time to put on the thinking cap. I figured this would be a concatenation formula. I would get the first word, assume that a space would be the delimiter to break apart the first word, search for the remainder of the cell and concatenate that with the first word along with a comma. There were couple of ways I could do this. I’ll explain  a couple of ways here along with what I finally sent him.

First off, a while ago, I found on the web some code that created a function in Excel called GETFIRSTWORD. The function looked like this:

Function GETFIRSTWORD(Text As String, Optional Separator As Variant)
Dim firstword As String
If IsMissing(Separator) Then
Separator = ” ”
End If
firstword = Left(Text, InStr(1, Text, Separator, vbTextCompare))
GETFIRSTWORD = Replace(firstword, Separator, “”)
End Function

In Excel 2007 or 2010, to add a user defined function (often called a UDF) you open the Developer Tab and click on Visual Basic. In earlier versions, you click on Tools – Macros. Once in VB you want to insert a MODULE. Give the module some name that makes sense. I always call mine “Myfunctions.” You can have more than one function in a module. Paste the code shown above into the Module. To use the function, you simply enter a the following in a cell: =GETFIRSTWORD(xx) where XX is the cell number.

The function assumes that a space separates the words but allows for delimiters as well. For example, say I had a string of words separated by semi-colons. The formula to enter into a cell would be: =GETFIRSTWORD(A1,”;”). Cool beans.

The next thing I needed to do was get the remaining part of the cell string. That was a simple search formula shown here: =RIGHT(A1,LEN(A1)-FIND(” “,A1)).

Using my function, the concatenation formula to build this would be:

=concatenate(RIGHT(A1,LEN(A1)-FIND(” “,A1)),”, “,GETFIRSTWORD(A1)).

Broken down, the concatenate is joining the remaining part of the string with a comma and the first word. Works like a charm. But then, I said to myself, could this be totally done in a formula without a function. And the answer was yes. Here’s what I came up with.

Basically I needed a formula to get the first word, and without the cool function, needed to put in what was the character or location to search and then pull all characters to the left of that character. In this case, it was a space. Therefore, I needed to find the first occurrence of a space in the cell. In the last sentence, the magic word is FIND. Ah ha, there is a FIND function in Excel. Using the FIND function, I had this:

=LEFT(A1,FIND(” “,A1)-1)

This said go look in A1, find a space, and show me everything to the left. Presto. I have my first word. Now, it was just a matter of merging together the two functions along with a concatenate command. This is what I created:

=concatenate(RIGHT(A1,LEN(A1)-FIND(” “,A1)),”, “,LEFT(A1,FIND(” “,A1)-1))

The concatenate command joined everything to the right of the first word, a comma, and then everything to the left of the first space. In the example above where you might have a string of data separated by semi-colons, you would replace the space with a semi-colon and get the same results.

Intriguing problem that was fun to figure out. I figured it would make a cool blog article.

Ciao, y’all.

Footnote: Here’s the link where I originally found the GETFIRSTWORD function. http://www.teachexcel.com/free-excel-macros/m-138,udf-get-first-word-cell-excel-free-macro.html

Advertisements

Written by pregen

July 3, 2012 at 5:11 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: