Let’s face it. Data in order forms or customer lists are often messy and unusable. Solopreneurs often lose precious time on manual tasks like reformatting names, addresses, and phone numbers. Check out these handy tips and tools that will help you save time and energy for more important things in your business.

Data collection is important for any business, big or small but misspelt words, an extra space here and there, a mix of upper and lower cases in names and phone numbers in different formats can often cause a major headache for Solopreneurs. It takes time to clean up and make the data usable but you could really use that time a lot more productively. Thankfully, there are handy functions and tools that will help you clean your spreadsheet data in less time, even if you’re no spreadsheet wizard.

---

Handy data formatting tricks for Google Sheets

Find and Replace

Make use of the Find and Replace function in Google Sheets to update all the formatting for commonly misspelt or mistyped data. This is great for cleaning up text that commonly gets misspelt (e.g. Albany vs Albny).

Common examples include city names (e.g. New York vs NY) and country names (e.g. uk vs United Kingdom).

1. On your spreadsheet, click Ctrl + f (⌘ + Shift + f on a Mac) or click Edit > Find and Replace in the menu.
2. In the “Find” section, type in the text that you want to reformat (e.g. NY).
3. In the “Replace” section, type in the correctly formatted text (e.g. New York).
4. Click the “Replace All” button. All the wrongly formatted text will be replaced with correctly formatted text.

Usefulness rating: 6/10

Find and Replace is very useful and helps to clean up formatting quickly but it only fixes 1 misspelling for each field at once. For example, if your data had New York spelt as new yrk, Ne York, new Yok, then you would need to do Find and Replace 3 times, once for each misspelling of the city name.

---

Text to Column

Sometimes, you might need to split the text in a cell and have some of the text in one cell and the rest of it in a second cell. A common example is splitting full names into columns for first name and last name, or product name into brand and model number.

This can easily be done using the text to column function.

1. Select the cells with the data that you want to split into multiple columns.
2. Click Data > Split text to columns.
3. Google Sheets will automatically detect how your text is split and put the data into separately columns. However, if you find that Google Sheets has not split the data correctly, look for the “separator” dialog box and select the correct separator from the list. Common separators are comma, tab, space, period and semi-colon. Google also allows you to select a custom separator if needed.

Usefulness rating: 7/10

Text to Column solves a frequently encountered issue when it comes to data in spreadsheets. With it, you can quickly separate data into different columns instead of cutting and pasting them one by one or re-typing everything. One drawback is that it’s not a perfect fix as it will put every string of text that comes after the selected separator into a new column. This might not always result in the formatting that you want.

For example, if you were splitting names and “James David Johnson” was one of the entries, Google would put James in column 1, David in column 2, and Johnson in column 3. If there was another entry for a “Sarah Thompson”. then Sarah would end up in column 1 and Thompson in column 2. This means that while both Johnson and Thompson are last names, they would not end up in the same column, which means the data is still not fully cleaned. You would need to take some extra steps to complete the cleaning process. It’ll still save you some time though.

---

Change Case

If you’re collecting data from form fills then you’re likely to be faced with text that comes in all kinds of capitalizations (or none at all). This can become a pain to deal with, especially if you’re sending emails and doing a mail merge or trying to personalize emails (looks bad to have a customer’s name improperly capitalized if you’re trying to personalize).

There are three ways to fix this in Google Sheets using formulas.

UPPER

The UPPER function will convert all the selected text into upper case. Create a new column where you want the cleaned data to be placed, and type the formula in the new cell. For example, if your original text is in cell A2 and you want the cleaned data to go into cell B2, then in cell B2, type =UPPER (A2).

You can quickly replicate this formula for other cells by clicking on the cell with the formula (cell B2 in our example) to select it, then mouse over the bottom right-hand corner of the cell and drag it downwards to apply it to other cells in column B.

LOWER

The LOWER function works in the same way as the UPPER function. Using the same example as above, you just need to type =LOWER (A2) instead of “UPPER“. This will convert all your text to lowercase.

PROPER

For mail merge/personalization, we’d typically want to have text in sentence case, and this can be easily done with the PROPER function. Again, if we used the same example as above, you just need to type in =PROPER (A2) instead of “UPPER“.

Usefulness rating: 8/10

Change Case is a handy fix for a very common data issue. The need to use formulas might seem a little daunting for those who’re less comfortable with typing formulas but it’s not a super complicated formula that can’t be mastered with a bit of practice.

---

Handy data formatting tricks for Microsoft Excel

Find and Replace

Find and Replace in Microsoft Excel is pretty much identical in function to its namesake in Google Sheets. You will be able to use this to fix common misspellings. The only difference is in the keyboard shortcut to access the function. Click Ctrl + h to open the Find and Replace dialog box. You can follow the same steps below to clean your data.

1. On your spreadsheet, click Ctrl + h.
2. In the “Find” section, type in the text that you want to reformat (e.g. new york).
3. In the “Replace” section, type in the correctly formatted text (e.g. New York).
4. Click the “Replace All” button. All the wrongly formatted text will be replaced with correctly formatted text.

If you’re on a Mac, you’ll need to go to the menu and click Edit > Find > Replace to access the dialog box.

Usefulness rating: 5.5/10

Find and Replace in Microsoft Excel does exactly the same thing as what the same function does in Google Sheets but it scores slightly lower as there is no keyboard shortcut available for Mac users, making it just a tiny bit more troublesome to access.

---

Text to Column

Text to Column is also available in Microsoft Excel and can be easily accessed in a similar way to Google Sheets.

1. Select the cells with the data that you want to split into multiple columns.
2. Click Data > Text to columns.
3. You will be prompted to indicate which type of separator should be used to split your text. You can choose between “delimited” (e.g. commas, tabs, spaces), and “fixed width” (manually select where to split).
4. If you selected “delimited”, you will be asked to select the exact delimiter in the next step. Pick the desired delimiter and “finish”. If you selected “fixed width”, you will be prompted to select exactly where you want the data to be broken. Drag the line to the place where you want the text to be separated and click “finish. This basically tells Excel to split the text at specific character counts.

Usefulness rating: 7.5/10

While the Text to Column function in Microsoft Excel and Google Sheets are basically the same, Excel offers the additional ability to split the text by character count. This can come in handy for very specific use cases. For example, if you need to ensure that text fits within a certain space for the printing of labels, or you’re preparing advertising copy and there is a fixed character count per row. These aren’t the most common scenarios though.

---

Change Case

Change Case works in exactly the same way on Microsoft Excel as it does in Google Sheets.

Usefulness rating: 8/10

This is one of the handier ways to fix spreadsheet data formatting issues so it’s worth learning and remembering the simple formulas.

---

These are just some of the handy ways you can clean your data quicker and free up time to work on other important Solopreneur stuff. Stay tuned as we add to this list!