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.
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”
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.
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.
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.
The formatted date is now in cell “E2”. Drag or copy the formula down the column to fill all cells with the new data.
Xem thêm Bài Viết:
- Oripan Gold Soap – Bánh rửa mặt trị mụn số 1 Hàn Quốc – Hotline: 19002236
- TOP 5 SẢN PHẨM TRỊ MỤN LƯNG HIỆU QUẢ | BODY CARE ROUTINE | LÂM À
- Hướng dẫn kẻ mắt cho người mới bắt đầu – EYELINER TUTORIAL FOR BEGINNERS |Ty Lê|
- Điều Chị Em Cần Nhớ Khi Uống Thuốc Tránh Thai Để Trị Mụn Trứng Cá
- Góc chia sẻ: 4 Cách gỡ bỏ tận gốc autocad một cách triệt để