Are you ready to become a mini-expert in using Google Sheets? One cool trick you can learn is how to use the Google Sheets TEXT function. This might sound a bit technical, but don’t worry – we will make it super easy for you to understand.
The Google Sheets TEXT function is like a magic wand that helps you change numbers into text in any format you want. Imagine you have a number, like your birthday, and want to make it look fancy in your spreadsheet. That’s where this handy function comes into play.
This article will explore step-by-step examples to show you how to use the Google Sheets TEXT function like a pro. It’s simpler than you think – you’ll be amazed at what you can do with it.
But before we get ahead of ourselves, let’s quickly understand the Google Sheets TEXT function.
Understanding Google Sheets Text Function
Have you ever used Google Sheets and needed to make numbers look a certain way? That’s where the TEXT function comes in. This function in Google Sheets is a unique tool that lets you change numbers into words or phrases. It’s useful when you want to show numbers in a particular style.
Let’s say you have percentages in your sheet. In Google Sheets, 30% is shown as 0.3, which can be a bit confusing. But with the TEXT function, you can make it look like ‘30%’, which is much easier to understand.
Not only that, but you can also use the Google Sheets TEXT function to change how numbers look in other ways. You can even mix numbers with words or symbols. For example, if you have a date, you can use this function to make it look the way you want, like ’12 Dec’ instead of just ’12/12′.
One thing to remember is that the Google Sheets TEXT function doesn’t take words out of numbers – it’s all about changing how the numbers appear.
Google Sheets Text Function Syntax; What You Should Know
In the previous section, we learned about what the TEXT function in Google Sheets is and how it can change numbers into a different style. Now, let’s understand the Google Sheets TEXT function syntax. Don’t worry, it’s pretty simple.
The TEXT function has a formula that looks like this:
=TEXT(num, format)
It needs two things (called ‘parameters’) to work:
- num: This is the number, date, or time you want to change. For example, it could be a date like your birthday or a percentage like 50%.
- format: This tells Google Sheets how you want to change the ‘num.’ You write this in quotes, like “mm/dd/yyyy” for dates.
When you write the format, you can use special symbols to get different results:
- 0: This makes sure zeros show up in your number. For example, if you write ‘050’, it will keep the zero at the start.
- #: This is like 0, but it won’t add extra zeros if they aren’t needed.
Here are some extra tips about using the TEXT function:
- You can’t use an asterisk (*) in your format.
- The function doesn’t understand the ‘?’ pattern.
- It doesn’t work with fractions.
- You can’t mix date and number formats together (like using ‘dd’ with ‘0’).
The TEXT function also lets you format dates and times in special ways. Here’s how:
- d, dd: These show the day of the month.
- ddd, dddd: These give you the day of the week.
- m, mm: These are for months or minutes.
- mmm, mmmm, mmmmm: These show the month’s name.
- yy, yyyy: These are for the year.
- HH, hh: These are for hours.
- ss, ss.000: These are for seconds or milliseconds.
- AM/PM: This shows if it’s morning or afternoon.
Remember, how you write these (like ‘d’ or ‘dd’) and capitalization matters to get the desired result.
Copy Sample Sheet
To follow along with today’s tutorial, feel free to copy our sample sheet using the link below.
How to Use the TEXT Function
Having explored a few things you need to know about the Google Sheets TEXT function, it’s time to get hands-on and show you how to maximize this helpful function when working with specific data in Google Sheets.
To simplify things, we will cover some examples in this section.
Read on as we break things down.
Example 1: Google Sheets TEXT Function and Time
Do you know that you can use the Google Sheets TEXT function to convert a 12-hour format to a 24-hour one without stressing? Well, read on, and we will show you how this works in a bit.
First, let’s clearly understand what we want to achieve. We have times in a 12-hour format (like “2:30:45 PM”) in Google Sheets. Our goal is to change this to a 24-hour format (like “14:30”) using the TEXT function.
Here is what our sample data looks like in Google Sheets:
With our sample data nicely put together, let’s show you how to use the Google Sheets text function to seamlessly convert time from 12-hour format to 24-hour format.
Step 1: Choose A Blank Cell
Let’s begin by choosing an empty cell in our spreadsheet. This is where we want the result of our conversion to appear. For this example, we will go with cell B2.
Step 2: Enter the TEXT formula
Having chosen an empty cell where you want the result of the conversion to appear (in our case, B1), it is time to enter the TEXT formula, which is pretty easy. What you want to do is navigate to the formula bar and type in the following formula:
=TEXT(A2, “HH:mm”)
What happens here is the TEXT function looks at the time in cell A2 (“2:30:45 PM”) and changes it to the 24-hour format (“14:30”).
Step 3: Press Enter
Now that the TEXT formula has been correctly entered as we described above, all you need to do is hit the Enter button on your keyboard. The result should be generated in the selected cell.
Here is what ours looks like:
What happened here is that the TEXT function looks at the time in cell A2 (“2:30:45 PM”) and changes it to the 24-hour format (“14:30”).
By following these simple steps, you can easily convert time from a 12-hour format to a 24-hour format in Google Sheets. This method can be used with any other time in your sheet. Remember, practicing with your own data will help you get the hang of it.
Example 2: Google Sheets Text Function and Date
In our first example, we learned how to use the TEXT function in Google Sheets to convert time from a 12-hour format (like “2:30:45 PM”) to a 24-hour format (like “14:30:45”). We used the formula =TEXT(A2, “HH:mm”) to make this change.
Now, let’s explore another example where we’ll convert a date format. We’ll start with a date in the “dd/mm/yyyy” format and change it to the “dd mmmm yyyy” format. This will change a date like “4/15/2023” to a more readable format like “15 April 2023”.
Here is the sample data we will use for this example:
Our aim is to change the date format in cell A2 from “4/15/2023” (day/month/year) to a more detailed format “15 April 2023” where the month is spelled out.
Step 1: Choose An Empty Cell
As always, we need to choose an empty cell in our spreadsheet. This is where we want the conversion result to show up. For this particular example, we will go with cell B2.
Step 2: Enter the Text Function
Remember how we entered the TEXT formula in the previous example? Well, we also need to do the same here.
To do that, we’ll use the TEXT function again, but this time for dates. The formula will look like this:
=TEXT(A2, “dd mmmm yyyy”)
What you want to do is head over to the formula bar and type the above formula.
Step 3: Apply the Formula
Once you enter the TEXT formula like we showed you in step 2, all that is left is to press the Enter button on your keyboard. Google Sheets should automatically generate the converted date in the cell you earlier selected.
If you did everything exactly as we showed you, you should see something like this:
What we have been able to do is take the original date from A2 and format it according to the pattern “dd mmmm yyyy”
By simply following the steps we detailed above, you’ll be able to convert the date from a numerical format to a more descriptive format, making it easier to read and understand. This method is handy for reports or documents where the date format needs to be more formal or detailed.
Example 3: Google Sheets TEXT Function and Currency Format
Besides using the Google Sheets TEXT function to convert time and date into a more understandable format, you can also deploy this function for other seamless format switches.
In our earlier examples, we used the TEXT function in Google Sheets to convert time from a 12-hour format to a 24-hour format and to change the date format to a more descriptive style. Now, we’ll tackle a new challenge: formatting currency.
In this example, we aim to change how currency is displayed in a cell, turning it into a whole number format.
Let’s say we have a currency value like “$123.45” in cell A2. We want to format this as a whole number, removing any decimal points and keeping the currency symbol.
Here is what our sample data looks like:
Now, let’s show you how to use the Google Sheets TEXT function to convert the currency format.
Step 1: Select the Cell for the Formula
First, click on the cell where you want the new formatted currency to appear. This could be any empty cell in your spreadsheet. For this example, we will go with cell B2.
Step 2: Enter the TEXT formula
With the cell where you want the formatted result generated selected, head over to the formula bar and type the following formula:
=TEXT(A4, “$#”)
Step 3: Press Enter
After typing the formula, press Enter. The currency in cell A2 will now be displayed as a whole number in cell B4. For our example, “$123.45” will be shown as “$123”.
Here is what that looks like:
Google Sheets Function: FAQs
Can the TEXT Function Make Numbers Look Like Words in Google Sheets?
Yes, it can. The TEXT function in Google Sheets is like a magician for your numbers. It can transform numbers into text. This is super helpful if you have data like dates, times, money amounts, or percentages and you want to display them in a specific way, like turning “123” into “one hundred twenty-three” or “4/5/2023” into “April 5, 2023”.
What Does the TEXT Function Do?
The TEXT function in Google Sheets is like your text-transforming tool. It changes numbers into text for various data types, such as dates, times, currency, and percentages. It also lets you decide how you want the text to appear.
How Do I Make the TEXT Formula Work?
To use the TEXT formula, just write =TEXT(num, format). Here, num is the number or the cell with the number you want to convert into text. format is the style you choose for displaying this number. Use ‘0’ in your format to keep all zeros, or use ‘#’ to skip displaying zeros at the beginning or end.
Final Thoughts
As we wrap up our journey through the world of the Google Sheets TEXT function, let’s take a moment to reflect on what we’ve learned. This function might seem a bit like a magic trick at first, but now you know it’s a practical tool to make your numbers look just the way you want in text form.
We explored how the TEXT function can change dates and times to different formats, turn currencies into whole numbers, and even display formulas as regular text. Remember, the key to using this function is understanding the two parts: the number you want to change (num) and how you want it to look (format).
Whether dealing with a birthday date, time for an event, or amounts of money, the TEXT function in Google Sheets helps you present your data clearly and in a style that makes sense for your project.
As a beginner, you might feel a bit overwhelmed at first, but with practice, you’ll find that the Google Sheets TEXT function is a reliable and versatile friend in organizing and presenting your data just how you need it.