If you have any type of spreadsheets that gets data in text form and has to present it in some way to clients, you surely know that in some cases you need to modify how the text is written. There are many different reasons why you’d need to do it. In some cases that I’ve dealt with, I needed users to write down either “Yes” or “No”. Of course, users would write in any of the following ways:

YES
Yes
Yes

It becomes very difficult to deal with such answers. One way to rectify that is to use excel functions that will transform the text to match the fomat that you’d like. Let’s take the following cases:

-need to use big caps: =UPPER(A2)

-need to use lower caps= LOWER(A2)

Then, you could want the “proper” format which will have first letters in words be caps and other letters be small caps. Here is what it would look like:

As you can see, it’s extremely simple but can prove to be very useful to better manage data that would otherwise be inconsistent.

Today I received an interesting question from a reader. He has a list of dates and needed to get the number of weeks and days between the two. Here is what he is looking at:

First off, I added a few more columns that you can see here:

The difference is obviously one minus the other. When you subtract dates in excel, it gives a date format. Ideally, I’d be looking at the number of days so I simply changed the format:

I get a number of days. I need to know how many weeks and obviously I do not want to have decimals and I need to round down in order to have the number of complete weeks. Here is how I’ll do it:

=ROUNDDOWN(D7/7,0)

This will round down to the number of weeks:

Then, the number of days will be:

Nb days = nb days – #weeks x 7

In this case:

=D7-E7*7

Then, I can simply add the difference as text by entering the following in G7:

=”The difference is ” & E7 & ” weeks and ” & F7 & ” days.”

You can see the result here:

Not that I want to be “fussy” but since this gives me a good opportunity, I’ll try to adjust text to account for the possibility that the number of days is 0 or 1.

If it is 0, I’d like to only write “X weeks”

If it is 1, I’d like to write “day” instead of “days”

So let’s start with the first condition. I’ll change:

=”The difference is ” & E7 & ” weeks and ” & F7 & ” days.”

To:

=”The difference is ” & E7 & ” weeks” & IF(F7=0,”.”,” and ” & F7 & ” days.”)

And the the final piece, by using a nested if condition:

=”The difference is “&E7&” weeks”&IF(F7=0,”.”,IF(F7=1,” and “&F7&” day.”,” and “&F7&” days.”))

You can see the end result here:

And download the spreadsheet here:)

Over the past few months, I’ve been interacting with many of you who have been visiting Experiglot, commenting on posts, sending me emails with questions and comments, etc. I’ve came to the conclusion that there were two big needs and today’s post is to announce that I am finally able to help out on both.

#1-Improve Your Excel Skills

Many of you have projects related to work or hobbies that require using excel either to improve, automate or even just to track and organize data. Searching this wensite and the web in general is great but we sometimes lack structure or don’t know what to look for. I’ve created an ebook that includes tens of functions along with a few spreadsheets wwith examples of vlookup, sumif, macros, etc. It’s a fairly large package that can enhance your excel skills and knowledge significantly, all for a very reasonable $9.95!!!

Find out more about the ebook here

#2-Get Instant Help For Your Spreadsheet

I know the feeling. You’ve been trying to get this thing done in excel for hours and maybe even days and it’s just not working. At this point, you need to get it done. I would be more than happy to help. You can send me your spreadsheet along with a description of what you’re trying to do. For a modest fee, I’ll take a look and get back to you within 24 hours. In over 90% of cases, you’ll have it fixed while more complex spreadsheets might require additional work or be beyond the scope of what I can reasonable expect to do.

This is guaranteed. If the spreadsheet does not do what you were looking for, I’ll be more than happy to pay you back.

Find out more about getting my help for your spreadsheets here

Today I wanted to introduce you to a very simple but also very useful function in excel; Concatenate. First off though, here is a context where I will use it. As is so often the case, there would be other ways to get this done but here I’ll use concatenate. First, here is my current set of data:

I would like to change the data to the following form:

FirstName LastName Country

so for the first name it would become:

Roger Federer SUI

First, I’ll get the 3 informations into 3 columns by using the following:

First name =MID(A2,FIND(“,”,A2)+2,FIND(“(“,A2)-FIND(“,”,A2)-3)
Last name =MID(A2,FIND(” “,A2)+1,FIND(“,”,A2)-FIND(” “,A2)-1)
Country =MID(A2,FIND(“(“,A2)+1,FIND(“)”,A2)-FIND(“(“,A2)-1)

The result becomes:

Then I can simply use the concatenate function to combine them by using:

=CONCATENATE(B2,” “,C2,” “,D2)

I also used the “find” and “mid” function a decent bit, you can find out more info about those in previous posts:) You can download the spreadsheet here.

It happens in a few circumstances. I bring data from the internet or some type of document and find myself with a bunch of data that I need to rearrange. Just take a look at this spreadsheet for example. When you look at it, it looks perfectly fine right?

Suppose that you need to change all of the “.TO” to “-CA”, how would you do it? You could use the right function. If the text to replace can be found anywhere in the string, you might need to use the find function as well.

Even easier though is the usage of the substitute function such as follows:

=SUBSTITUTE(TEXT, text to find, text to replace it with)

So in this case that becomes:

=SUBSTITUTE(A2,”.TO”,”-CA”)

You can see the result here:

And download the spreadsheet here