How to use text to columns like an excel pro

Bottom Line: Learn how to split a column of names into first and last names with the Text to Columns feature of Excel.

Skill Level: Intermediate

Watch the Tutorial

Download the Excel File

If you’d like to practice using the same workbook I use in the video, you can download the file here:

Split Names with Text to Columns.xlsx

Splitting Names into Separate Columns

It’s really common to have a column of full names that you want to separate into a first name column and a last name column. Excel has an easy built-in feature to do this called Text to Columns. It allows you to split cells by character. It has some limitations compared to other options, which I’ll talk about more at the end, but I’d like to walk you through how to use Text to Columns for this task.

As you can see, I’m starting with a simple table that has a Full Name column.

How to use text to columns like an excel pro

My first step is to duplicate the column. I’m doing this because I want to keep a column for the full name in addition to having columns for both first and last names. If you don’t want to keep a Full Name column, you can skip the duplication step.

To duplicate the column, just copy the column and paste it into a blank column.

How to use text to columns like an excel pro

With the new column selected, click the Text to Columns button on the Data tab.

How to use text to columns like an excel pro

This will open up the three-part Text to Columns Wizard. For the first step, ensure that the Delimited button is selected and hit Next.

In the second step, select Space for the type of delimiter that we want to split by. This will break up the first names from the last names because they are already separated by a space. You’ll notice that the wizard gives a little preview for how the split will look.

How to use text to columns like an excel pro

Nothing is required on the third step of the wizard, so just hit Finish on the second step.

You now have two columns instead of one. Of course, you can rename the columns to First and Last and move them around in your table if you wish.

How to use text to columns like an excel pro

Some Disadvantages of Text to Columns

As you can tell, this method for splitting columns is super easy and is great for quick, one-time uses. But if you expect your source data to constantly have edits or additions, it’s not the best method.

In that case, I recommend using Power Query to split your columns. I’ve demonstrated how to do that in this post: How to Split Cells and Text in Excel with Power Query. Using Power Query, updating the data in your split columns is instantaneous whenever you refresh your query. Using Text to Columns, you would essentially have to delete your new columns and start again if you wanted to update.

Another instance of the Power Query method being superior to Text to Columns is when some of the Full Name entries include a middle name.

When we use Text to Columns and some of the entries have middle names, the names get split into three columns, which is good. But for the entries that don’t have middle names, the last name gets put into the second column instead of the third, which is bad.

How to use text to columns like an excel pro

Using Power Query, you can split off the the last names first, then the first names can be separated, leaving the middle column populated only when it’s applicable.

Ways to Split Text

Here are the links to the other posts on ways to split text:

Conclusion

Again, I would recommend Power Query over Text to Columns in most cases, but I want you to know Text to Columns because it’s an easy and simple solution when you just need to split two names on a data set that won’t require updates.

I hope this explanation for splitting cells by a delimiter in order to extract names has been helpful for you. I welcome your questions and feedback in the comments. Until next time!

Using Text to Columns to Separate Data in a Single Column

Data, Data Tools, Text to Columns can be used to separate data in a single column into multiple columns, such as if you have full names in one column and need a column with first names and a column with last names. When you select the command, a wizard dialog box opens to help you through the process. In step 1 of the wizard, select whether the text is Delimited or Fixed Width (see the next sections for definitions of Delimited and Fixed Width). In step 2, you provide more details on how you want the text separated. In step 3, you tell Excel the basic formatting to apply to each column.

If you have data in the columns to the right of the column you are separating, Excel overwrites the data. Be sure to insert enough blank columns to not overwrite your existing data before beginning Text to Columns. See the section “Inserting an Entire Row & Column” in chapter 2, “Working with Workbooks, Sheets, Rows, Columns, and Cells,” for instructions on how to insert columns.

Working with Delimited Text

Delimited text is text that has some character, such as a comma, tab, or space, separating each group of words that you want placed into its own column. To separate delimited text into multiple columns, follow these steps:

  1. Highlight the range of text to be separated.
  2. Go to Data, Data Tools, Text to Columns. The Convert Text to Columns Wizard opens.
  3. Select Delimited from step 1 of the wizard, as shown in Figure 3.6, and click Next.

How to use text to columns like an excel pro

Figure 3.6. Select the Delimited option to separate text joined by delimiters, such as commas, spaces, or tabs.

How to use text to columns like an excel pro

Figure 3.7. Select the Delimited option to use the comma as a delimiter between the city, state, and ZIP Code.

If you need more than one delimiter but one of the delimiters is used normally in the text, such as the space between city names and the space between a state and ZIP Code (Sioux Falls, SD 57057), consider running Text to Columns twice: once to separate the city (Sioux Falls) from the state ZIP Code (SD 57057) and again to separate the state and ZIP Code.

How to use text to columns like an excel pro

Figure 3.8. Using Text to Columns with a comma delimiter separated the city from the state and ZIP Code. Run the wizard again on the state ZIP Code column with a space as a delimiter to split up that data.

Working with Fixed-Width Text

Fixed-width text describes text where each group is a set number of characters. You can draw a line down all the records to separate all the groups, as shown in Figure 3.9. If your text doesn’t look like it’s fixed width, try changing the font to a fixed-width font, such as Courier. It’s possible that it’s fixed-width text in disguise.

How to use text to columns like an excel pro

Figure 3.9. Use the Fixed Width option when each group in the data has a fixed number of characters.

To separate fixed-width text into multiple columns, follow these steps:

  1. Highlight the range of cells that includes text to be separated.
  2. Go to Data, Text to Columns.
  3. Select Fixed Width from step 1 of the wizard and click Next.
  4. Excel will guess at where the column breaks should go, as shown in Figure 3.9. You can move a break by clicking and dragging it to where you want it, insert a new break by clicking where it should be, or remove a break by double-clicking it. Click Next.

Don’t worry about leading spaces—Excel will remove them for you.

How to use text to columns like an excel pro

Figure 3.10. In step 3 of the wizard, set the format of each column. If you have numeric text, such as ZIP Codes, make sure you configure the wizard to treat the column as text so you don’t lose any leading zeros.

Are your exported data dates are not in regional dates? Are you finding it difficult to replace this feature? Did you know the data type of your date can be changed? Then this article will help you to convert text to date format using Text to Columns option. Let’s get started.

Set Regional Dates with Text to Columns – An Introduction

Generally, when you download data from online like any financial data, the dates wouldn’t be of the same format. Though they view like dates, the data would actually be a text. To find whether the data is in text or date format, you can make use of a filter drop-down menu or number format drop-down menu.

Example: Here I have downloaded a financial file and all the dates are not in the region and I have to change it to regional dates. Now we will find whether the data is in text or date format using a filter drop-down menu. The steps for using the filter drop-down menu are as follows:

1. Select any cell within the table and go to Home > Sort & Filter > Filter.

Pro Tip. You can also make use of the shortcut key to apply the filter. Select any cell and press Ctrl + Shift + L to apply the filter.

2. Once you click Filter, the filter will be applied to the headers in the form of drop-down menu.

3. You can click on the drop-down arrow to see the filtering options for the headers.

Once I click the Date (Header) Filter, You can see the Date column is not grouped by month and year but all the dates are formatted in texts.

The next option to find whether the data is in text or date format is by using a number format drop-down menu. The steps for using the number format drop-down menu are as follows:

1. Select any cell in the date column within the table and go to Home > number format drop-down menu.

2. Once you click on the number format drop-down arrow, it will show you the list of options to format your cells.

Here, you can see that the dates and the numbers are the same in the formatting options. This is because Excel is not able to identify the data as a date.

I will just show you an image of the formatting option which considers the cell as a date. The below image will help you to comprehend better.

Convert Text into Data Format using Text to Columns Option:

The options like Find and Replace doesn’t really work in formatting dates because of the difference in regions. Alternatively, the Text to Columns feature works no matter which part of the world you are. The steps to convert text into date format are as follows:

1. Select the texts that you want convert into dates.

Pro Tip. To do it quickly, Select the data first cell and press the shortcut key Ctrl + Shift + Down Arrow Key (on the keyboard)

2. Then go to Data (Tab in the ribbon) > Text to Columns.

3. Once you select Text to columns, a dialog box appears in the window. It consists of 3 steps.

4. In first step, Select the delimited option in the dialog box and click Next.

5. In step 2, Since the Tab option is already selected, don’t make any changes and click Next.

6. In step 3, Select the Date option and choose Format where the data at present shows. That is if the text is Day – Month – Year then choose DMY but if it is Month – Date – Year then choose MDY. Then click Finish.

7. Once you click Finish, you can see that the text is converted into date format.

In the below image, you can see that the data is changed from text to date format. I have used a filter drop-down menu to show you the difference in the formatting option. Once I click the Date (Header) Filter, You can see the Date column is grouped by month and year because it has considered the column as a date format.

Note. Since Text to columns sets the dates per the region automatically, you do not have to concentrate and changing the region/language in the Microsoft settings.

Refer to the linked articles to learn more about filter and number format options.

You can use the Split Text to Columns in Google Sheets to quickly split the contents of a cell (or a range of cells).

Split Text to Columns feature comes in handy when you want quickly split the first name and the last name, or the username and domain name from email id, or the domain name from URLs.

In this tutorial, I will show you multiple examples of how to split text to columns in Google Sheets. Soon you will know exactly how to split cells in Google Sheets like a pro.

This Article Covers:

How to Split Text to Columns in Google Sheets

Example 1 – Split the Full Name into First Name and Last Name

Below is a dataset with the names of some of my favorite superheroes:

How to use text to columns like an excel pro

Here are steps to split the full name into the first and last name:

  • Select the data that you want to split.
  • Go to the Data tab.
  • Click on Split Text to Columns from the drop-down.How to use text to columns like an excel pro
  • In the Separator dialog box that appears at the bottom right of the data, select space as the delimiter.How to use text to columns like an excel pro

That’s it! It will instantly split the names into the first name and the last name.

How to use text to columns like an excel pro

  • When you use the ‘Split Text to Column’, it overwrites the original data set. If you want to keep the original dataset intact, create a copy of the data set and use Split Text to Column on that data set.
  • It would give you a static result. This means, that your data would not update in case you update the original dataset. If you want this to be dynamic, use the split function.
  • Every space character is considered as a different separator. In case you have a double space between names and you use the space character as the delimiter, it will split the name into 3 columns. In such cases, remove the double space by using the TRIM function [there is a text to column functionality in Excel to consider consecutive delimiters as one. I hope that is also adopted by Google Sheets].

Example 2 – Split the Email id into Username and Domain Name

Suppose you have a dataset with emails as shown below:

How to use text to columns like an excel pro

Here are the steps to use Split Text to Columns to separate username and domain name:

  • Select the data that you want to split.
  • Go to the Data tab.
  • Click on Split Text to Columns from the drop-down.How to use text to columns like an excel pro
  • In the Separator dialog box that appears at the bottom right of the data, select Custom.How to use text to columns like an excel pro
  • In the Custom field, enter @. How to use text to columns like an excel pro

As soon as you enter @, Google Sheets will instantly split the text into username and domain name.

Again, remember that this will overwrite the original dataset. If you want to keep the original data set intact, create a copy, and then use the Split Text to Columns feature.

Example 3 – Get the Domain name from the URL

Suppose you have a dataset as shown below:

How to use text to columns like an excel pro

Note that there is a mix of URLs where some only have the root domain and some have links to a specific page/post.

Here are the steps to get the domain name from URLs using Split Text to Columns:

  • Select the data that you want to split.
  • Go to the Data tab.
  • Click on Split Text to Columns from the drop-down.How to use text to columns like an excel pro
  • In the Separator dialog box that appears at the bottom right of the data, select Custom.How to use text to columns like an excel pro
  • In the Custom field, enter /How to use text to columns like an excel pro

Note that as soon as you enter /, the URLs will spit and the domain name would be in column C.

Now if you’re wondering why column B is empty, it’s because there are two forward slashes after HTTP in the URLs. Each forward slash is treated as an individual separator.

Also, note that this technique works when you have the domain names in the same format. For example, if you have one with HTTP and one without it, then it may give you the domain names in different columns.

So this is how you can use the Text to Columns functionality in Google Sheets to quickly split cells in Google Sheets. You can also do similar text to the column using formulas (such as RIGHT, LEFT, MID, etc.). but in most cases, I find using this a lot easier.

I hope you found this tutorial useful!

You May Also Like the Following Google Sheets Tutorials:

When you are working with data in Microsoft Excel, it may sometimes be useful to split cell information such as names (Doe, John) and addresses (Eau Claire, WI) into multiple columns so you can work with each separately. This document offers instructions for splitting cell data.

Commonly, this type of data is separated by a comma or space. These characters, used to separate data within a cell, are called delimiters. Excel allows you to split data based on a number of predefined delimiters or you can specify your own.

Considerations

  • Review the cells to standardize the cell data and the delimiters. This will help to ensure a clean separation of data. In other words, you may have this combination: UW-Eau Claire, UW Milwaukee, and University of Wisconsin, Platteville. Using the Find and Replace feature can make this type of “clean up” easier.
  • Review the cells for separators also used as text characters. For example, Eau Claire WI has a space in its name but also uses the space to separate city and state. This may influence what character you use as a delimiter.
  • Review cell contents to determine if you need to split your data into three columns (e.g., Jane M. Doe or John Doe Jr.).
  • Review the cells for delimiters that appear consecutively (e.g., the comma and space in Canton, OH). If your cell data has consecutive delimiters, you will probably want to select the Treat consecutive delimiters asoneoption. Excel will act as if there were one delimiter instead of two and will not split the data into extra cells. If you use a space as one of your delimiters, Excel automatically selects Treat Consecutive Delimiters As One. If you do not wish to use this option, deselect it.

WARNING: Splitting data writes over both the source data cells and the cells directly to the right of the source data. If there is data in these cells you do not want to lose, you may insert a blank column(s) to the right of the column you are splitting. For more information on adding columns, refer to Working with Rows and Columns.

Splitting the Data

Select the cells containing the data you want to split.
NOTE: Excel will split only one column at a time.

From the Data menu, select Text to Columns.
The Convert Text to Columns Wizard – 1 of 3 dialog box appears.

Under Original data type, select Delimited.

Click Next.

Under Delimiters, select the character(s) you want to use to separate the cell data.

(Optional) Select or deselect Treat consecutive delimiters as one as appropriate.
NOTE: For more information, refer to Considerations above.

Deselect any delimiters you do not want to use.
The Data preview box shows you what the split data will look like.
NOTE: The Text qualifier pull-down list allows you to select the symbol you wish to use to indicate that the data included within is to be treated as text.

Click Next.

  1. Windows: In the Destination text box, click Collapse Dialog.
    Macintosh: In the Destination text box, click Collapse Dialog.
    The mouse pointer will become a cross.
  2. Select the cell(s) where you want the split cells to appear.

Windows: To return to the Convert Text to Columns Wizard, click Restore Dialoge.
Macintosh: To return to the Convert Text to Columns Wizard, click Expand Dialog.

Click Finish.

If there is any existing data in the cells where the split data will be placed, a dialog box appears asking if you want to replace the content of the destination cells.
To replace the data, click OK .
To cancel the cell split and leave the cells as they were, click Cancel.

Was this article helpful? Yes No

Connect with UWEC
University of Wisconsin-Eau Claire

Excel’s Text to Columns feature allows you to separate the text that is contained in a single cell and split it into multiple columns. For example, a full name appears in one cell (e.g., Doe, John). You can use Text to Columns to put the last name in one column and the first name in the next column. This would make it easier to use in a mail merge.

Another example would be city and state information appearing in one cell (e.g., Eau Claire, WI). You can use Text to Columns to put city names in one column and state abbreviations in another. This makes sorting by state easier.

This data must be separated by delimiters, such as commas or spaces. Excel allows you to separate cell data either by selecting predefined delimiters or using custom delimiters.

Considerations

Here are some things to consider before separating your cells:

To ensure consistent data separation, standardize all cell data and corresponding delimiters. Using Find and Replace (Win | Mac) can make standardization quicker and easier.

Do not use delimiters that also function as text characters.
EXAMPLE: In Eau Claire WI, the space between Eau Claire and WI could work as a delimiter. But there is also a space operating as a text character between Eau and Claire, which you would not want to separate. One solution could involve separating cities and states with commas and using the comma as your delimiter.

Determine if you should separate your data into three columns (e.g., Jane M. Doe or John Doe Jr.).

Look for consecutive delimiters (e.g., the comma and space in Canton, OH). If your cell data has consecutive delimiters, you should select the Treat consecutive delimiters as one option.

NOTE: If you use a space as one of your delimiters, Excel automatically selects Treat Consecutive Delimiters As One. If you do not wish to use this option, deselect it.

WARNING: Separating data overwrites both the source cells and the cells directly to their right. To avoid deleting adjacent cell data, you can insert one more blank columns to the right of the column you are separating. For more information on adding columns, refer to Working with Rows and Columns (Win | Mac).

Separating the Data

Select the cells containing the data you want to separate.
NOTE: Excel can only separate one column at a time.

Windows only: Select the Data command tab.

Windows: In the Data Tools group, click Text to Columns.
Macintosh: From the Data menu, select Text to Columns.
The Convert Text to Columns Wizard dialog box appears.
NOTE: Your data is displayed in the Preview of selected data section.

Under Original data type, select Delimited.

Click Next.

Under Delimiters, select or deselect the appropriate delimiters.
The Data preview box shows you how your data will be separated.
NOTE: If you would like to keep some data containing your delimiter from being separated, the Text qualifier pull-down menu allows you to select the symbol you wish to use to indicate that the data included within is to be treated as text.

(Optional) As appropriate, select or deselect Treat consecutive delimiters as one.
HINT: For more information, refer to Considerations above.

Click Next.

  1. In the Destination text box, click Collapse Dialog.
  2. Select the cell(s) where you want the separated data to appear.

To return to the Convert Text to Columns Wizard, click Restore Dialog.

Click Finish.
If there is any existing data in the cells where the separated data will be placed, a dialog box appears asking if you want to replace the content of the destination cells.

Was this article helpful? Yes No

Connect with UWEC
University of Wisconsin-Eau Claire

How to use text to columns like an excel pro

This page will show you how to convert Excel data into a table.

Creating a Table within Excel

  1. Open the Excel spreadsheet.
  2. Use your mouse to select the cells that contain the information for the table.
  3. Click the “Insert” tab > Locate the “Tables” group.
  4. Click “Table”. A “Create Table” dialog box will open.
  5. If you have column headings, check the box “My table has headers”.
  6. Verify that the range is correct > Click [OK].
  7. Resize your columns to make the headings visible.

Changing the Table Style

  1. Click on a cell in the table to activate the “Table Tools” tab.
  2. Click the “Design” tab > Locate the “Table Styles” group.
  3. Choose a style/color option that appeals to you. (Hover over the various table styles to see a live preview.)

Keywords: Office, color, colors, filter, sort, rows, columns, apply, enhance, table

Share This Post

Blog Resources

Cedarville offers more than 150 academic programs to grad, undergrad, and online students. Cedarville is known for its biblical worldview, academic excellence, intentional discipleship, and authentic Christian community.

How to use text to columns like an excel pro

Cedarville University

for the Word of God and the Testimony of Jesus Christ

How to use text to columns like an excel pro

Do you know what comes before data analysis? Data integrity. Well, it should anyway. Because if your data is rubbish, then your analysis will be.

Before you can do any analysis with a dataset, it should be correct, consistent, and complete. Otherwise, you could end up with a Kodak moment. Not the good kind, the $11m mistake kind .

Here are a few tips for dealing with a dirty dataset.

Removing Duplicates

One of the most common challenges with data is duplicates. Dealing with duplicates can be straightforward. But before we start, it’s a good idea to take your dataset and make a copy. Deleting data is permanent, so it’s always safer to keep a copy of what you started with.

Now, if your dataset is full of duplicates rows, you can highlight the entire dataset and navigate to the Data tab in the Excel ribbon. There, you can click remove duplicates and, voila – Excel will delete duplicate values so that only the first remains. Many of us are familiar with that process, but we think you should take a step back and do some due diligence before removing duplicates.

Two rows may share many values but be separated by others. Take this example:

In a dataset containing membership contact details, two rows may contain the same email address, but the names in each row are different.

There are a couple of different scenarios that may explain this.

  • The names are of two family members who share an email address, but are of course two distinct people – two members.
  • It’s one person who has input two different variations of their name—Bob and Robert, for example. Or perhaps a simple typo – Robert and Roebrt.

If we’ve selected the whole dataset, Excel doesn’t see these rows as duplicates. So we could end up with excess data, giving false impressions about the number of members.

So how do we account for things like this? Well, one way is conditional formatting. We could highlight a column – let’s say the ‘email’ column – then Navigate to the ‘Home’ tab on the ribbon, and click the ‘Conditional Formatting’ dropdown. Hover over ‘Highlight Cell Rules’, then click ‘Duplicate Values’.

How to use text to columns like an excel pro

Now, select Data > Filter from the excel ribbon. Click the down arrow in the heading of the ‘email’ column, then Filter by colour, specifically the colour used to highlight duplicate values. You’ll be left with the duplicate emails, and can spot check for similarities and differences between rows before choosing which duplicates need removing.

I recently downloaded and opened a CSV file from a web browser and one of the data columns was supposed to have date information. There was date-type information, but Excel recognized it as being a text field.

I really needed a valid date range for graphing purposes, so I used the Text to Columns feature to parse the data and convert a date field at the same time. It worked quite well.

In general, here’s how you accomplish this task:

  • Select the range, or column that contain the text values
  • From the Data tab, Data Tools group, click Text To Columns
  • Follow the directions in the Text to Columns Wizard

Using the Text To Columns Wizard

There are three steps in the Text to Column Wizard. In the first step, the wizard correctly surmised that my data had spaces between each field and was of the Fixed Width type.

How to use text to columns like an excel pro

In the second step I wanted to combine the month and day fields into one column.

How to use text to columns like an excel pro

Double-clicking the break line between the Month and Day fields allowed me to combined these fields.

How to use text to columns like an excel pro

In the third step I elected to import all but the second column. This is done by selecting a column header and clicking Do not import column (skip). Notice that all the column headers are labeled with the General format.

How to use text to columns like an excel pro

The second column, containing the date text, can be formatted as a date by selecting the Date option button and then choosing your format, which I left as MDY.

How to use text to columns like an excel pro

Click Finish to execute.

If you are not happy with the results you can undo everything by using the keyboard shortcut Ctrl+Z.

View the steps in this video.

Again, you can undo everything the Wizard has done by using the keyboard shortcut Ctrl+Z.

Update: Warning

This method works because the Year value supplied by the computer’s System Date is the same as the data in column A. This method will fail to give you the correct Date from any year other than 2010. Check out how to extract a Date using a formula.

3 thoughts on “ Text To Columns in Excel ”

Your post is dealing with a simple case of taking all the info from one text column and spreading it out with Excel’s Text to Columns functionality. A good primer for what I’m doing here in my post, parsing out specific data in the text beforehand to get the date/time elements to work.

If you need to conditionally split values into multiple columns in Microsoft Excel, consider using the IF() function. Here’s how.

How to use text to columns like an excel pro
Illustration: Lisa Hornung, Getty Images/iStockPhoto

We all inherit Microsoft Excel sheets that don’t suit our working routine. If it’s a simple tracking sheet of some sort, you can usually change things to suit your working needs.

Windows: Must-read coverage

  • Windows 11 cheat sheet: Everything you need to know
  • Windows Server IoT 2022 is for a lot more than Internet of Things
  • Windows 11 SE: Why it’s both more and less locked down than Windows 10 S
  • Windows 11: Tips on installation, security and more (free PDF)

For instance, it’s not uncommon to work in a sheet that follows traditional database design rather than a financial sheet structure. When this happens, you can usually revamp the structure by employing a few expressions instead of creating the new structure from scratch. In this article, I’ll use two IF() functions to split a simple “database” sheet into multiple columns, based on a value in another column.

Disclosure: TechRepublic may earn a commission from some of the products featured on this page. TechRepublic and the author were not compensated for this independent review.

I’m using (desktop) Office 365, but you can work with earlier versions. For your convenience, you can download the demonstration .xlsx file, but you can also work with your own data. The expressions will work in the browser edition.

Why you might use the IF() function

In database fashion, a lot of data is defined by type. That way, you only need one field for the actual value. You can do this with Excel, too, as shown in Figure A. This simplified table uses only one value field and defines each value as either a debit or a credit. In Excel, you might also see this as two value columns, one for debits and one for credits, and no transaction type field because that’s identified by each column header.

Figure A

How to use text to columns like an excel pro

If you inherit a sheet that resembles the one in Figure A, but you want a more traditional sheet where the two transaction types are in different columns, you’re not stuck. You might try Text to Columns first, but in this case that feature won’t work because it works with a single column. You can’t use it to split values based on a conditional value in a second column. Instead, we’ll use two IF() functions, one for debit and one for credit.

The IF() function uses the following form:

where condition is a value or expression that equates to true or false and the true and false arguments identify what happens when condition is true or false, respectively. Now, let’s put this function to use.

How to make the debit expression

There are several string functions for delimiting strings, but we need a conditional expression. For that reason, we’ll use two different IF() expressions: One that returns debit value and one that returns credit values.

Let’s start with the debit values. First, create a new column by entering Debit in cell E2. If you’re working with a Table object (as I am), Excel will automatically extend the Table. In E3, enter the following expression:

Copy the expression to the remainder of the column. Figure B shows the two debit values in column E.

Figure B

How to use text to columns like an excel pro

At this point, column E contains only existing debit values from column D. If the value in column C equals “Debit,” the function returns the true argument, which is the corresponding value in column D. Otherwise, the function returns a blank string (“”). As you can see, column E contains only two values, the values where the corresponding text in column C is Debit. Now, we need a column for credits.

How to make the credit expression

Now that you’ve split out the debit values with an IF() expression, you can probably do the credit values on your own. In F2, enter Credit, and again the Table object will extend the Table. We’ll use the same basic function, but this time, we’ll use Credit as the condition:

As you can see in Figure C, we now have two columns: Column E displays debit values, and column F displays credit values. But we’re not done yet.

Figure C

How to use text to columns like an excel pro

The difference between formulas and values

Right now, it looks like we’re done, but we really aren’t. The values in columns E and F are the results of two IF() functions, and we need literal values.The IF() functions are only a temporary means of getting the existing values into the structure we want to use going forward.

To convert the formulas, do the following:

  1. Select E3:F6 (the range containing the IF() functions).
  2. In the Clipboard group (on the Home tab), click Copy.
  3. In the same group, click the Paste dropdown and choose Values (the first option) in the Paste Values section (Figure D). After doing so, the values in columns E and F are now literal values—the IF() functions are gone.

Figure D

How to use text to columns like an excel pro

How to clean up your columns

Now that you have the original values split into columns, you can clean things up a bit. First, you’ll want to format the new values in columns E and F. In this case, apply the Currency format. Second, you should delete or hide columns C and D—they are no longer needed. Figure E shows the finished results. Now, users can enter debits and credits in columns E and F, respectively.

Figure E

How to use text to columns like an excel pro

How to use text to columns like an excel pro

Microsoft Weekly Newsletter

Be your company’s Microsoft insider by reading these Windows and Office tips, tricks, and cheat sheets.

Sometimes, analyzing data that is stored in a crosstab format can be difficult in Tableau. When working with Microsoft Excel, text file, Google Sheets, and .pdf data sources, you can pivot your data from crosstab format into columnar format. If you are working with other data sources, you can Pivot using custom SQL (Tableau Desktop).

For example, suppose you have the number of devices sold by quarter for three vendors in three separate fields. You can pivot your data so that the vendor is in one field and the number of devices sold is in another field.

How to use text to columns like an excel pro

Pivot the data

After you have set up the data source, in the grid, select two or more columns. Click the drop-down arrow next to the column name, and then select Pivot . New columns called “Pivot field names” and “Pivot field values” are created and added to the data source. The new columns replace the original columns that you selected to create the pivot.

How to use text to columns like an excel pro

Add to the pivot

To add more data to the pivot, select another column, click the drop-down arrow next to the column name, and then select Add Data to Pivot . Make sure that the pivot columns and values look as expected before you begin your analysis.

How to use text to columns like an excel pro

To remove a pivot, click the drop-down arrow next to the name of a pivot column, and then select Remove Pivot .

Troubleshooting pivots

Red fields in the view and fields with exclamation points in the Data pane: Because the original fields are replaced with new pivot fields, any references to the original fields in the view will no longer work. They cause fields to become red in the view or show a red exclamation point next to the field in the Data pane.

Null values in the grid: If all of the original fields used in the pivot are removed, for example in an extract refresh, null values display in the pivot fields.

No pivot option: Pivot appears when you select two or more columns in a single Microsoft Excel, text file, Google Sheets, and .pdf data source. If using a different data source in Tableau Desktop, you can use custom SQL to pivot.

Pivot using custom SQL (Tableau Desktop)

You can also use custom SQL to pivot your data, even if you aren’t working Excel, text file, Google Sheets, and .pdf data sources. When you use the UNION ALL operator in a custom SQL query, you can take values from distinct columns and put them into a new column.

For example, suppose you have a table called Contest.

Runner Start Time End Time
Amanda 9/3/2016 3:04 PM 9/3/2016 3:25 PM
Oscar 9/3/2016 3:04 PM 9/3/2016 3:21 PM
William 9/3/2016 3:04 PM 9/3/2016 3:16 PM

To optimize your analysis of this data in Tableau, you can use the following custom SQL query to pivot the “Start Time” and “End Time” columns so that their values are in a single column.

The above custom SQL query does the following:

  • Pivots the Start Time column header into a string value called Start and adds that value to a new column called Action.
  • Pivots the End Time column header into a string value called End and adds that value to a new column called Action.
  • Pivots the Start Time and End Time columns so that their values are in a new column called Time.

The following table shows the results of this custom SQL query.

To pivot data using custom SQL

Connect to your data.

Double-click the New Custom SQL option in the left pane. For more information, see Connect to a Custom SQL Query.

In the Edit Custom SQL dialog box, copy and paste the following custom SQL query and replace the contents with information about your table:

Where the following is true:

Static Column: A comma-delimited list of the columns from Table, both dimensions and measures, which should not be included in the pivot.

New Value (from Column Header 1-3): New names that you give to the original column headers, which are used as row values in the pivot.

Pivot Column Values 1-3: The columns whose values need to be pivoted into a single column.

New Column Header: The name you give the new column that contains the new row values from New Value (from Column Header 1-3).

New Values: The name give the new column that contains the original values from Pivot Column Values 1-3.

Hello all and welcome back to the Excel Tip of the Week. This week we have a Creator level post in which we’re taking a definitive revisit to the handy data transformation tool, Text to Columns.

What is this feature all about? How do you use it?

Text to Columns is a very long-standing data transformation tool that can help parse data in one column and separate it into several. It’s particularly commonly used when dealing with data imported from other programs, for example comma-separated values.

For an example, let’s take a look at this sample sales data:

We can convert this single-column data by selecting it and then using the Text to Columns Wizard, which is accessed from Data => Text to Columns:

The options here are for how the new columns will be created – “Delimited” means that each column is marked by some particular character, and “Fixed width” is for cases where the data has been lined up visually and essentially needs cutting into strips. Here we are dealing with delimited data.

On the left here we can see where we identify which character is the delimiter. A selection of common options is given, or we can specify our own. This example also shows us how we can use the “text qualifier” field: In our original data, some of the customer names – e.g. ABC, Incorporated – include commas. Normally this would cause a problem, as the two parts of the name would get separated – but in our case, the customer names are all wrapped in quotation marks. By setting the quotation makes as a text qualifier, we can tell Excel to ignore any delimiter characters between them. We can see in the live preview that this will get us the result we want.

Most of the time you can just stop here and press Finish, but for completeness’ sake here’s the final step in the Wizard:

If you do use this step, you can specify a format for each new column that will be created. You can also use the “Destination” box to specify a location for the new columns – by default they are placed over the top of the original data, overwriting it, but if you prefer you can place them elsewhere and retain the original data as well.

The Advanced option here is interesting to consider:

This lets you control how Excel interprets numeric data – useful if e.g. you have been supplied European number format data such as 4.123,06 and want to have Excel convert into UK / US standard. It can also handle trailing minus signs to convert e.g. 4,123.06- into a proper -4,123.06.

Once you are all done, Finish will output the completed data:

This lets you control how Excel interprets numeric data – useful if e.g. you have been supplied European number format data such as 4.123,06 and want to have Excel convert into UK / US standard. It can also handle trailing minus signs to convert e.g. 4,123.06- into a proper -4,123.06.

Once you are all done, Finish will output the completed data:

Note that Text to Columns is a programmatic step – it does not generate formulas or any kind of audit trail. If you are worried about errors in the transformation process, use the Destination box as described above to output the separated data separately from the original data.

Additional quirks

It’s worth noting that you can do essentially the reverse of Text to Columns using the TEXTJOIN function:

You can also automate the process of taking a set of data and splitting it using Power Query’s Split Column transformation:

This has the advantage of being repeatable if you paste in new data later on.

Finally, you might be wondering if it’s possible to split data this way formulaically – the answer is “No” with basic formula options, but Google Sheets does have a SPLIT function that can do it – although without the ability to declare a text qualifier:

How to split a cell by each line of data within it; this will put each line into its own separate cell. Technically, this method is Text-to-Columns using a carriage return as the delimiter.

(Carriage return is the term for the element that puts text onto a separate line within a cell in Excel.)

How to use text to columns like an excel pro

Sections:

Split Text based on Carriage Return

Basically, we use Text-to-Columns with Ctrl + J as the delimiter to tell Excel to split data based on the individual lines within a cell.

  1. Select the data to split.
  2. Go to the Data tab and click Text to Columns
  3. Select Delimited and click Next.
    How to use text to columns like an excel pro
  4. Check next to Other and, in the blank box next to it, hit Ctrl + J. This is the shortcut that represents a carriage return; basically, this is the internal thing that represents a new line within a cell in Excel.
    How to use text to columns like an excel pro
    Note: when you type Ctrl + J, you will not see anything appear in the box, it will remain visibly empty. However, you should see the separate lines from the cell appear separated, as in the Data preview window in the above image.
  5. Click Next if you want to change where the data will go or its format; click Finish when you’re done. Make sure to select a cell to the right of the current cell/column for the Destination option if you do not want to overwrite the original cell that contains all of the data in one cell.
    How to use text to columns like an excel pro
  6. That’s it!

How to use text to columns like an excel pro

Notes

This is annoyingly simple and annoyingly easy to forget; almost never in Excel will you use the shortcut Ctrl + J, but that’s all it takes to separate data by carriage return (new line).

To create a carriage return (new line) within a cell, hit Alt + Enter while editing a cell.

Download the sample file to see the above example in Excel.

Question? Ask it in our Excel Forum

Excel VBA Course – From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

VBA Course – Beginner to Expert

Subscribe for Weekly Tutorials

BONUS: subscribe now to download our Top Tutorials Ebook!

The link to our top 15 tutorials has been sent to you, check your email to download it!

(If you don’t see the email, check your Spam or Promotions folder and make sure to add us as a contact so you get our emails in the future.)

Excel VBA Course – From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

Knowing how to split and join text in Excel is a highly useful skill that will allow you to work efficiently and accurately.

Splitting Data

Data in a cell can be split into separate, adjoining cells based on a character that separates the parts, such as a space between the first and last name, or after a specified number of characters.

To Use Text to Columns:

How to use text to columns like an excel pro

  1. Select the cell(s) to be split
  2. DATA >> Data Tools group >> Text To Columns
  3. Select Delimited or Fixed Width and click Next
    • Delimited—click select the delimiter
    • Fixed Width—click where the cell should split
  4. Choose the appropriate delimiter

How to use text to columns like an excel pro

Note: The Text to Columns feature overwrites data in the column(s) to the right so be sure to Insert the proper number of columns before performing the action.

Joining Text

There are two primary means of joining text in Excel:

1. The Concat function

  • =CONCAT(Text1,Text2,…)
  • The Concat function, previously known as “concatenate”, will fully replace the concatenate function in the next version of Excel

2. By using ampersands (&)

  • The “&” symbol is used to link different text string together
  • Example: A1&” “&B1 would result in the contents of A1 and B1 separated by a space

Learn More About Excel

Become a Microsoft Excel pro through our suite of Microsoft Office classes in NYC and Excel training courses. We offer flexible scheduling and small, hands-on courses at all levels of experience.

Related Resources

4 Advantages Gained by Attending Microsoft Excel Classes

Microsoft Excel is a powerful spreadsheet and data analysis program with wide-ranging capabilities. In this article, we will review the advantages gained by attending our Microsoft Excel training classes.

10 Core Formatting Techniques in Excel

In this guide, we review the top 10 formatting techniques you need to know to present clean and polished work.

Excel Navigation and Paste Special

Work smarter, not harder, in Excel. Learn about keyboard shortcuts for fast, efficient navigation, and use Paste Special to copy complex items and paste them into the same worksheet or another worksheet using only specific attributes of the copied data.

We can use the Text to Column tool to separate values that are not similar into separate columns and rows. This tool helps us to split our data into different columns of the excel sheet. This tutorial will walk all levels of Excel users through the process of Converting Text to Columns and Rows.

How to use text to columns like an excel proFigure 1 – How to Convert Excel Text to Columns and Rows

How to Convert Text to Column

  • We will use Figure 2 to illustrate the process of converting text to column.

How to use text to columns like an excel proFigure 2 – Data to convert text to column

  • We will select the cells containing our data

How to use text to columns like an excel proFigure 3 – Select the cells with the data

  • We will click the Data tab and click Text to columns

How to use text to columns like an excel proFigure 4 – Click Text to Columns

  • We will click next

How to use text to columns like an excel proFigure 5 – Convert Text to Columns Wizard

  • We will check the Comma and Space boxes

How to use text to columns like an excel proFigure 6 – Check the Comma and Space boxes

  • We will click Finish

How to use text to columns like an excel proFigure 7 – Prompt to replace destination cells

  • We will click OK

How to use text to columns like an excel proFigure 8 – Text converted to Columns

How to Convert Text to Rows

Excel does not have the Text to Row tool like Text to Column. We will use the Transpose tool to convert our Text to Rows.

  • We will select and Copy the data

How to use text to columns like an excel proFigure 9 – How to Convert Text to Rows

  • We will select a location where we want our new data to be pasted.

Figure 10 – Select location of new data

  • We will right-click and select the Transpose tool under Paste options

How to use text to columns like an excel proFigure 11 – How to convert Text to Rows

Instant Connection to an Expert through our Excelchat Service

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

I have data in a column that needs to be split into multiple columns using Text to Columns in the Data tab. However, there will be a variable amount of columns created for each row, variable from 1 – 1000. In the Text to Columns wizard Step 3, it defaults to ‘General’ format for all columns. My data involves very big numbers, and I do not want them trailed (rounded), so I need to set the format as Text.

This is a problem to do manually because I would have to go through every column and click Text, and the wizard doesn’t even show all the columns unless you scroll. Ideas?

3 Answers 3

Although it is not exactly what you want (and I see you don’t like the idea of scrolling), when you are presented the data preview showing each column as General.

You can select the first column header and holding the Shift key scroll to the right and select ALL of the columns at once.

Then select Text as the data format. Not the best, but better than selecting each individually.

How to use text to columns like an excel pro

Click the first column as you would do normally to change its format to text, but then scroll to the last column. Hold down Shift and click the last column.

This should select all the columns. Now, change the format and finish 🙂

I have a related problem and I have discovered a work around solution.

I have a .TXT from MYOB (accounting package). It has some document header information in the first six rows then the table header and the data. On the Import Wizard it is only showing me two columns (which matches the document header information in the equivalent of A1 and B1) so I can’t set the third column to “TEXT” instead of “GENERAL”. It is treating the MYOB account codes as dates which is causing no end of headaches.

I’ve done the same plenty of times with these MYOB .TXT files before and Excel has picked up the table header row without problems but for some reason it is now having trouble on this file. Even more strangly it has successfully done it on one of the .TXT files I am trying to use. I can’t see what I am doing differently.

WORKAROUND SOLUTION (BTW: “Using Excel 2010”) I want the header information but I first set up the import stating at row 7, set the import type for each column and run. Then I go back to ‘Data’ -> ‘Connections’ -> ‘Properties’ -> ‘Definition’ Tab -> ‘Edit Query’ then reset to start at row 1 and click finish and it worked. Beats me why.

Numbers and formulas aren’t the only important data in Microsoft Excel. Text entries are also a key part of worksheet data. Whether the text is a column heading or a critical data element, Excel includes many ways to improve the layout, appearance, and value of text. Find out how to format text cells, combine data from multiple cells, and other Excel text tips.

1. Formatting Text Cells

How to use text to columns like an excel pro

If you are having problems sorting or filtering data lists, one of the reasons may be that some data in a column is formatted as text and some as numbers. In an ascending sort order, numbers and punctuation will sort before text. For instance, entries of 22, 1111, 333 would sort as 22, 333, 1111 if formatted as a number, and 1111, 22, 333 if formatted as text (left-to-right). You will get inconsistent results when a combination of formats exists. The Text format is also needed for data that includes a fixed number of places or leading zeros such as 0001234.

One plus in Excel is you can format cells prior to entering data. This is true for text as well as numbers. To format entire columns for text entry, highlight the column(s), then right-click on the column and choose Format Cells. Next, pick the Number tab from the dialog box, and then select the Text format, finish with OK. The Text alignment choice is also found on the Home tab (Alignment group) of the Ribbon. Apply the Text format to existing cells as well for better sorting and filtering results.

2. When Formatting Doesn’t Work

To change a single entry to a text format, type an apostrophe (‘) in front of the data.

3. Combining Two or More Cells (Sounds Like Cat)

Existing data may not be formatted exactly the way you need. For example, if employee names are in 2 columns, Last Name and First Name, but you want to display a full name for reporting as in Last Name, First Name (Smith, Joe) there are several ways to do this, the CONCATENATE or CONCAT functions and the ampersand (&). In the following examples, Column B is Last Name and Column C is First Name. To create the Full Name in Column D, the formula would be similar to (either example works):

=CONCAT(B2,”, “,C2) <—– CONCAT is a function that “combines”; additional text like the comma is included in quotations; CONCAT replaces the CONCATENATE function in Excel 2016/2019 as well as Excel 365. The CONCATENATE function will continue to be supported with all versions of Excel.

=B2 & “, ” & C2 <—– the & also combines the values in cells B2 and C2.

4. Another Use of the Ampersand (&) in Excel and Access

In the above example, the ampersand (&) is a character used to combine or add multiple entries. In Excel as well as Microsoft Access, the & is also a special code which means a single ampersand won’t work in most text entries (the result is _ instead). To use an ampersand in a header, footer, or label in Excel or Access, type 2 ampersands, such as TIPS && TRICKS.

5. Wrapping Text in Excel

For multiple line entries of labels, comments, notes, or descriptions in Microsoft Excel, select the cells (or an entire row or column), right-click on the selection and choose Format Cells, pick the Alignment Tab, and check the option for Wrap Text, finish with OK. In Excel , Wrap Text is also found on the Home tab of the Ribbon. The column width will determine how many lines are needed to display the text. Choose this over multiple rows for lengthy text entries for greater readability and for any lists of data. Once text is formatted to enable wrapping, you can further control the layout by pressing [Alt] + [Enter] to force a line break or new line within the same cell.

Try these tips and tricks to master your text entries in Microsoft Excel. Discover 6 more tips to work with text in Excel.

Discover more Excel shortcuts, tips and tricks here.

© Dawn Bjork, MCT, MOSM, CVP, The Software Pro®
Microsoft Certified Trainer, Productivity Speaker, Certified Virtual Presenter

For more information about plan types and included capabilities, see the Smartsheet Plans page.

To apply formatting to numbers and text in your sheet, use the buttons on the toolbar at the top of the Smartsheet window.

For certain views or screen sizes, not all buttons will be visible on the toolbar. If you don’t see a button that you need, click More .

TIP: Many of the tasks you do with formatting buttons can also be done with keyboard shortcuts. For a list of keyboard shortcuts, see the Keyboard Shortcuts article.

  • The thousands and decimal separators for numbers are controlled by the Language (Country) settings in your Regional Preferences. To change these setting, use the instructions in the Change the Language and Date Notation article.
  • Column headers are always formatted in white, bold text with a grey background. You cannot change this style.
  • Formatting will be automatically applied to a cell if it’s directly above, below, or between two cells that contain the same formatting and are on the same indent level. You can learn more on this in our Auto-Fill Formatting and Formulas article.

Format Groups of Data

You can format groups of data all at once. Follow the guidance below to help with this.

To apply formatting to Take this action before you click a formatting icon
An entire row Select the row number. Doing this will apply row formatting to both existing and newly added cells in the row.
An entire column Select the column header. Doing this will apply column formatting to both existing and newly added cells in the column.
An entire sheet Highlight all columns in the sheet by clicking the left-most column’s header, scrolling to the right, then Shift+clicking the right-most column’s header. When you release your click, all of the columns and rows in the sheet should be highlighted and you can then click the desired formatting icon.

Date Formats

You can apply different date formats (for example, long form dates) with the steps in Apply a Standardized Date Format in Your Sheet.

Indent and Outdent

Indent rows to create an organized structure to your sheet, with child rows that fall under a parent row. You can use these hierarchical relationships along with formulas, for example, to automatically calculate a weighted percent complete on a parent task based on data in the child tasks. Details about how to do this on this are included in the Parent Rollup article.

For more information on creating hierarchical relationships, check out the article on using hierarchy.

Font and Cell Formatting

Draw attention to certain elements of your sheet by setting various font and cell background formats. For example, you can set bold font for parent rows to make them stand out more.

After selecting an area of the sheet, you can change font type and size, background color, as well as alignment and text wrapping in cells.

Wrap Text

Select the cell in which you want to wrap text and click Wrap Text .

Clear Formats

Use Clear Formats
to return your selection back to the default font and cell background. This is useful for quickly removing formatting from a section of your sheet without removing the data in the cells.

Format Painter

To quickly copy an existing format on your sheet to other cells, use the Format Painter tool:

  1. Select the cells that contain your desired format.
  2. Click Format Painter .
  3. To instantly copy that formatting to a new destination, select the desired new cell.
    TIP: Double-click the Format Painter button to lock the format. You can then continuously apply the locked format to cells. Click the Format Painter button again, or press Esc to release the locked format.

Automatic Formatting

To apply formatting automatically, create conditional formatting rules or use Highlight Changes.

Conditional Formatting

To automatically apply formatting based on the content in a cell, click Conditional Formatting and create conditional formatting rules.

Use a conditional formatting rule, for example, to change the background color of a row based on the person in the “Assigned to” Contact column. Learn more about setting rules to automate the application of your formatting in the Conditional Formatting article.

Highlight Changes

Enable Highlight Changes to gain visibility on recent edits made to your sheet. Details on this are available in the Highlight Changes article.

Numeric Formatting

Buttons for formatting numbers are grouped together.

If you don’t see these buttons on the toolbar, you may need to click More .

Click Currency Format to automatically format numeric values with the appropriate currency symbol and decimal position.

Use Percentage Format to treat numbers as percentage of 1. If you format a cell this way, the value 0.95, for example, will be is displayed as 95%. The value 95 will be displayed as 9,500%.

You can enable Thousands Format to include the thousands separator and decimal.

Click Increase Decimal or Decrease Decimal to move the decimal point.

I’m using Excel Online within Office 365 and the Data tab is missing tons of things. Text to Columns is a function I use all the time and it’s not there. Anyone know if there’s a different way to do Text to Columns in Excel online?

Only work around I have is to do it in Excel desktop and then copy/paste into Excel Online but I shouldn’t have to do that. 🙁

Popular Topics in Microsoft Office 365

6 Replies

HDCautism

Do you have the option to “Edit in Excel” ? on the top center /right of your excel online?

Yes, and I tried that, but then it causes all kinds of conflicts if multiple people are editing the document at the same time when some are online and I am not. Also, my Excel seems to not want to open it all the time and hangs and freezes.

I guess copying and pasting will be the best option. heh.

HDCautism

Yes if you want multiple people to be able to edit it at the same time, everyone needs to do use excel online.

Be advised, if you do the copy/paste to existing data, you might (accidentally) overwrite some edits from your colleagues. I would create a “maintenance window” during which you can work with the file in Excel desktop mode, and if they lose their word during the maintenance window, they just have to do it over.

Best of luck to you.

Don’t expect Excel Online to be an exact replicate of Excel 2016. It’s not quite.

Yeah. I keep finding things I use all the time that aren’t there. Don’t even see a way to do a customer sort. 🙁

I’m just coming across this now, but there is an add in for that. It’s called Split Text to Columns and it works for Office365

This topic has been locked by an administrator and is no longer open for commenting.

To continue this discussion, please ask a new question.

IT User Walk-in Guidelines ideas

So I’m new hire and part of my goals this year it create a list of suggestions to bring up to Management on how our IT area handles walk-ins The purpose is the following: To make sure everybody is acknowledged as they walk into our area To guide them to .

Computer security/password resources for users?

Hi!Are there websites I can point my users to in order to help them with creating better passwords? I know Diceware is one but what else? I also just found SANS OUCH! Anything similar?

Spark! Pro Series – 22 March 2022

Today in History: 22 March 1765 – Stamp Act passed; 1st direct British tax on American colonists, organized by Prime Minister George Grenville 1782 – Pope Pius VI arrives in Vienna to meet with Holy Roman Emper.

Snap! Okta Breach, Microsoft Hacked, HP Printer Flaws, Exoplanets, Ingenuity

Your daily dose of tech news, in brief. You need to hear this. Authentication firm Okta probes report of digital breach Authentication provider Okta seems to have suffered a serious data breach, after screenshots of sensitive appeared onlin.

How do you measure success?

If you have a new engineer in your team and you get them trained. How do you measure success in regards to the engineer in terms of the training you have provided? How do you gauge what they need to be able to do or to know if your training has been succe.

How to use text to columns like an excel pro

Microsoft quietly replaced the comfortable Text Import Wizard from Excel and replaced it with the “Get & Transform” tools. The “Get & Transform” tools offer a lot of options and are very powerful. Unfortunately, they are quite complicated to use. Here is what you should now.

In a hurry? Click on “File” –> “Options” –> “Data” and set the corresponding checkmarks for reactivating the “Text Import Wizard” in Excel. Start the text import by clicking on “Data” –>”Get Data” –> “Legacy Wizards” –> “From Text (Legacy)”.

Introduction

In Excel 365 (only) 2016 (since version 1704) the “Text Import Wizard” was removed. It was replaced by the powerful “Get & Transform” tools. The “Get & Transform” tools also provide a function to import text and CSV files into Excel.

You have the following two options:

  1. Luckily, the comfortably “Text Import Wizard” still exists. You can re-activate and use it for importing text and csv files into Excel.
  2. Use the import function of the “Get & Transform” tools.

Restore the “Text Import Wizard”

The good news: You can easily restore the “Text Import Wizard”. Unfortunately, the option for re-activating them is hidden.

Follow these steps:

  1. Click on File and then on “Options”. Go to “Data” on the left-hand side.
  2. In the lower section of the window you can select the wizard you’d like to restore. For only importing text- or csv-files, select “From Text (Legacy)”. Feel free to also activate the corresponding wizard for importing Access files, files from web, from SQL servers and so on.
  3. Confirm with OK.

Now, you can find the so-called “Legacy Wizards” in the “Get Data” drop-down menu. In order to use them, follow these steps:

  1. Go to the “Data” ribbon.
  2. Click on “Get Data” on the left-hand side.
  3. Next, go to “Legacy Wizards”.
  4. Click on “From Text (Legacy)”.

How to use the “Text Import Wizard”

How to use text to columns like an excel pro

The steps for using the “Text Import Wizard” in Excel are shown in the screenshots.

  1. Go to the “Data” ribbon and click on “From Text”. If you have a recent Excel version and there is no button called “From Text” (but instead “From Text/CSV”), click on “Get Data”, then on “Legacy Wizards” and then on “From Text (Legacy)”. Please refer to the paragraph above if this option is missing.
  2. Select how you want to define the columns: Either with a character as a separator or with a fixed width.
  3. If the first row contains headers, check the corresponding box.
  4. Continue with “Next >”.
  5. Select the delimiter. This is the character dividing the data into columns, for example “Tab”, “Semicolon” or “Comma”.
  6. Usually text fields use quotation marks marking the beginning and end of a text field.
  7. For each column, you can choose the data format. For dates, you could define the order of days, months and years.
  8. Click on “Advanced”…
  9. …for defining decimals and thousands separators.
  10. Finalize the import by clicking on “Finish”.

Import text and csv files with the “Get & Transform” tools

How to use text to columns like an excel pro

How to use text to columns like an excel pro

How to use text to columns like an excel pro

Importing text files in Excel with the “Get & Transform” tools requires many steps. Please refer to the numbers on the screenshots:

Dates can be awkward in Excel. The most prevalent format worldwide is Day-Month-Year (DMY), but not all countries follow it. One such country you might have heard of that differs from this “standard” is the US, where it is commonplace to use Month-Day-Year (MDY).

Speaking from personal experience, I remember one project manager was nearly fired after he thought the deadline was 1 March 2015 when it was in fact 3 January 2015. This is the danger of 1/3/15, for example.

To show you how to overcome this problem, I will illustrate with converting US dates to what is often known as the “European” date format. Now, I know many readers would prefer this to be the other way around. I apologise, but I am an Australian Brit with the appropriate regional settings on my machine, and it’s a little awkward to perform screenshots that way. Don’t worry though — just follow me in reverse.

The problem becomes significant when you receive date data in a spreadsheet that is not recognised by your regional settings — or worse, actually is, like my unfortunate project manager mentioned above. For me, my computer cannot make sense of US date formats such as those shown in the screenshot below.

How to use text to columns like an excel pro

I have left the data in “General” style deliberately so you can see only one entry, cell A4, is recognised as a number (date). The problem is, even that’s wrong as that represents 5 December 2022, not 12 May 2022.

How do I convert it? We could use Power Query / Get & Transform — but that’s not really what this article is about. There is an easy way in Excel — but first, let’s start with a hard way.

In the screenshot below, I have managed to fix the issue:

How to use text to columns like an excel pro

See? Easy. Oh, sorry, I didn’t display the formula I used to do this in the image. Here it is for cell C2:

I have provided the formula because I am frustrated by the number of times I have read on the internet that this is not possible formulaically. Rubbish. You would just be a little insane to do it that way.

I won’t explain this formula. Suffice to say it only works for converting US dates to European dates; the text strings are delimited with “/” and do not contain “@” in the text string. If you want the conversion to go the other way, simply replace d/m/yy in all instances above with m/d/yy.

Having said that, I think we are all agreed we need another — simpler — way. Let’s start again. Back to the original data, I make a copy in cells C2:C11, as shown in the screenshot below.

How to use text to columns like an excel pro

I do this so I may retain the original data (it’s always best to keep a copy in case you make a mistake). Next, I highlight cells C1:C11 (including the header) and click on Text to Columns in the Data Tools grouping of the Data tab of the ribbon (Alt+A+E):

This generates the Convert Text to Columns Wizard dialog box. In Step 1, choose the Delimited option and click Next.

How to use text to columns like an excel pro

This means the data will be split into columns based upon a specified delimiter. Except we are going to cheat and not do that. In Step 2, uncheck all delimiters and then click Next, as shown below:

How to use text to columns like an excel pro

Now we come to the step that we actually want. We don’t use the Text to Columns feature to split data into separate columns. No, I want Excel to recognise my data as dates.

How to use text to columns like an excel pro

In this final step, select the Date: option in the Column data format and choose the date format that matches the data as it currently isnot what you want it to be. You are asking Excel to recognise it. In my case, the data is in Month-Day-Year format (MDY), so this is what I selected. Once you have chosen, click Finish.

How to use text to columns like an excel pro

I think you will agree this is far simpler than the formulaic approach and, more importantly, works for all date scenarios — as long as the original dates are formatted consistently.

As you keep working with dates, you will appreciate more and more the need for consistent dates — and the fact that they really aren’t that difficult to manipulate once you know the tricks.

A text string, also known as a string or simply as text, is a group of characters that are used as data in a spreadsheet program. Text strings are most often comprised of words, but may also include letters, numbers, special characters, the dash symbol, or the number sign. By default, text strings are left-aligned in a cell while number data is aligned to the right.

Note:

The information in this article applies to Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel for Mac, and Excel Online.

Format Data as Text

Text strings usually begin with a letter of the alphabet, but any data entry that is formatted as text is interpreted as a string.

Convert Numbers and Formulas to Text with the Apostrophe

Text strings are also created in Excel and Google Sheets by entering an apostrophe ( ) as the first character of data.

The apostrophe is not visible in the cell but forces the program to interpret whatever numbers or symbols are entered after the apostrophe as text.

For example, to enter a formula, such as =A1+B2, as a text string, type:

The apostrophe, while not visible, prevents the spreadsheet program from interpreting the entry as a formula.

Convert Text Strings to Number Data in Excel

At times, numbers copied or imported into a spreadsheet are changed into text data. This causes problems if the data is being used as an argument for some of the program’s built-in functions, such as SUM or AVERAGE. Options for fixing this problem include using Paste Special or the Error button.

Convert Text to Numbers With Paste Special

Using paste special to convert text data to numbers is relatively easy. It also keeps the converted data in its original location. This is different than the VALUE function which requires the converted data to reside in a different location from the original text data.

Convert Text to Numbers Using the Error Button

As shown in the image above, the Error button, or Error Checking button, in Excel is a small yellow rectangle that appears next to cells that contain data errors. You’ll see it when number data formatted as text is used in a formula.

To use the Error button to convert the text data to numbers:

Select the cell(s) containing the bad data.

Select the Error button (yellow exclamation sign) next to the cell to open a context menu.

Select Convert to Number.

The data in the selected cells is converted to numbers.

Concatenate Text Strings in Excel and Google Spreadsheets

In Excel and Google Spreadsheets, the ampersand (&) character joins together or concatenates text strings located in separate cells in a new location. For example, if column A contains first names and column B contains last names of individuals, the two cells of data can be combined together in column C.

The formula that does this is:

The ampersand operator doesn’t automatically put spaces between the concatenated text strings. To add spaces to a formula, surround a space character (entered using the space bar on the keyboard) with quotation marks.

Another option for joining text strings is to use the CONCATENATE function.

Split Text Data into Multiple Cells With Text to Columns

To do the opposite of concatenation, to split one cell of data into two or more separate cells, use the Text to Columns feature.

To split data in a cell, follow these steps:

Select the column of cells containing the combined text data.

Select the Data tab.

Select Text to Columns to open the Convert Text to Columns wizard.

Select Delimited and select Next.

Choose the correct text separator or delimiter for your data, such as Tab or Space, and select Next.

Choose a column data format, such as General, and select Advanced.

Choose alternative settings for the Decimal separator and Thousands separator if the defaults, the period and the comma respectively, are not correct. After you’ve made your changes, select OK.

Select Finish to close the wizard and return to the worksheet.