In this post, we will teach you the correct way with which you can achieve the separation of a text string within a spreadsheet and its step-by-step method so that you can learn it correctly and also faster. These types of methods will serve you not only in work areas, but also in school, where you can implement your knowledge and thus do everything in an excellent way, including even different graphics.
Continue in this post, so that you can acquire greater knowledge of Excel, in addition to learning more about the tools and functions that this program saves.
What are text strings?
Now, this tool called ‘Text string’ or also known simply as ‘Text’ is expressed with a group of characters which are used as data within a spreadsheet, in this case, within Excel.
These text strings usually have a variation in their composition, which will depend on the database that is being handled at the time. Said variation of characters will vary (forgive the redundancy), between numbers, letters and special characters such as the point, dash/dash symbol and finally the numeral or numeric symbol, which may be a regular expression.
Separation of text strings
For it to work we must explain how this tool works and its variables to apply it in a spreadsheet and its formulas correctly. By having a text string that must be separated, I could employ the use of formulas or text command in columns.
In this opportunity, we will teach you two methods of text separation in the Excel sheet, so that you can use both; We will begin by briefly explaining the two types of separation, which are made up of:
- Separation by delimiter character: it is that text string that contains a symbol or character that indicates the place where the division will be executed. These are understood by the characters; white space, comma, semicolon, tab or other type of symbol/letter that allows visualizing the separation in the text.
- Separation by defined width: it is the function that allows to make ‘Cut’ in the text chain for each determined number of characters. Therefore, the string does not need to be delimited by symbols, it will only be divided based on the number of characters that make it up.
Now, keeping in mind how this Excel capability works, let’s learn how to separate a text string with some practical examples:
First of all, we will perform the separation of a text string by a defined width, so the purpose is to separate the text string into two parts: to do this, use the ‘left’ function to extract the first three digits of the string : ‘=left (a2, 3) ‘and now to display the rest of the digits in another column, we will use the ‘right’ function like this: ‘=right (a2, 10) ‘.
And in an easy and fast way, we will have our text string divided into two columns with the numbers displayed.
To perform a different operation, let’s proceed to split the text data into two or more number of cells than required separate. To achieve this, select a column of cells that has the merged text data and click the ‘Data’ menu, then click ‘Text to Columns’ again to open the ‘Convert Text to Columns’ wizard.
Now select the column of cells with the data and click on ‘Delimited’ and press ‘Next’ to proceed. Followed by this, choose ‘Correct Text Delimiter’ in the corresponding data.
Once this is done, we choose a format for our data in ‘Column data format’ and when we reach the ‘Advanced’ button option we configure the alternatives for the Decimal and Miles separator (keep in mind that this will be done if the data is not correct).
To finish, we will only click on ‘Finish’ to close the program wizard and return to our spreadsheet, to observe at this point that our column must now be separated into two or more columns, without neglecting special functions such as that of ‘Extract’.
