Microsoft Excel Concatenate, Text to Columns, LEFT and RIGHT functions

2
12



Instructions

Text to Columns

Choose delimiters based on how your data is separated. In our example, I want to split the data everywhere there is an underscore “_” and a comma so this is what I selected. Because underscore is not one of the choices, I’m entering that character in “other”. You can see in the preview that there is now a line where there were underscores and commas, indicating where the data will be split.

When you make your selection, a description appears to help you decide if that is the format you’re looking for.

The “General” tab is right for our data example.

The “Advanced” tab gives you advanced text format options.

When you’ve made your selections click “Finish”.

Use Text to Columns to get rid of the “.dat” in the Date column and the “X-” in the type column. To do this, I’ll use the “Left” and “Right” functions.

The LEFT and RIGHT functions are setup in the same way but Left keeps the characters at the beginning of the string, Right keeps characters at the end of the string.

You’ll need to insert a column next to the column you want to change. Click on the first cell of the new column – below the heading.

Click on the – functions icon. When the functions box appears, type RIGHT in the search box and click “Go”. Do this for any function you want to use.

Click “OK”

The Functions Argument box will appear and guide you in setting up any function you want to use.

The “Text” field is typically your cell name or range. I clicked on the first cell in the “Type” column and dragged my cursor down to the last cell in order to select the data. This filled the field with the range.

To the right of the fields Excel has shown me what I’ve selected and shows the result.

To determine the number to enter in the “Num_chars” field (number of characters), I counted all of the characters in what I want to keep, “Internet Business”.

Note: Spaces count as characters.

Any time that you have a formula (and functions are just formulas) you have to remove the formula once it has filled the cells, or when you delete your original column, you’ll get the “#ref”.

To do this, simply click on the column that has the formula to highlight it.

Right-click your mouse and from the drop-down, click “copy”, right-click again, and select “paste special”, select “values”, then click “OK”.

Now you can delete the original column.

With Concatenate, you can combine cell data in any order you want, and even add characters.

In this example, I’m going to take my new Date column and use the concatenate function to change the format.

First, I’m going to use Text to Columns function in a slightly different way to split the existing data.

The data will be split into three parts, to accommodate this, insert two empty columns.

Highlight the column, in this case, the Date column.

Go to the “Data” tab, click on “Text to Columns”.

In the pop-up box, Select “Fixed width”

Click “Next”

In the new box, I’m able to click in the preview box and manually move the break lines where I want my data separated.

The next box allows you to format, but the default “General” is usually sufficient.

Click “Finish”

This pop-up appears which reminds you that you could have data in the way when Excel splits the data. If you’ve inserted columns to accommodate the data, click “OK”.

Excel has split the data in three columns and I have inserted another empty column – column “E” to accommodate my concatenated data.

In the new Date column, click in the first cell beneath the header.

Click on the functions icon, and type in “Concatenate” and click “OK”.

The default for the Function Arguments box is two fields but when you fill in both fields another field with appear.

Note:

It doesn’t matter what column the data is in.

In my example the year is in column “B”, the day is in column “D”, and the month is in column “C” and I want the date in MM/DD/YYYY format.

Click in the “Text1, then click on the first cell of each column to populate the fields.

Example: for the “Text1” field, I clicked in cell C2 on my worksheet, etc.

Enter in the order you want. I also want to add dashes to my date. To add anything to or between your concatenated data, you put it in the field in the order you want it to appear. Excel will add the quotes.

Click “OK”.

The formatted date is now in cell “E2”. Drag or copy the formula down the column to fill all cells with the new data.

Remember to copy/paste/values in the new column so that you can delete the split columns.

Nguồn: https://dantrionline.com.vn

Xem thêm bài viết khác: https://dantrionline.com.vn/lam-dep/

2 COMMENTS

  1. This video contains examples and instructions on the use of Text to Columns, Concatenate, LEFT and RIGHT functions. I hope you find it useful.

  2. With the numbers below say every "6" is highlighted. The first "6" to be found would be included in "360". What would be the formula to find the number to the left (3), the right (0) the top (2) and the bottom (0). The "360" is actually in three separate columns but can be concatenated to appear in one. The problem I am having is having Excel recognize the highlighted "6" and then perform the function of finding the number to the left, right, top and bottom. Understand that at times there may not be a number to the left or right depending on the location of the "6". Do you have any suggestions? Any aid would certainly be appreciated.

    542
    188
    724
    360
    906
    542
    188
    724
    360
    906

LEAVE A REPLY

Please enter your comment!
Please enter your name here