This, in essence, is a smarter way to copy and paste the data into new cells.Ĭlick inside the first cell of the appropriate column-the one named “First, in our example-and type in the first name of the first person in your dataset. If you only have a few names, and you don’t want to mess with the Text to Columns Wizard, you can use Flash Fill instead. If this happens, just click inside the “Destination” area within the wizard or add the information manually into the Destination field. You may notice a chime and then an inability to select the cell you want to move the data into. For example, if you want to split the email address to different parts the common element in the email address is in all the email id’s, so becomes delimiter here. Excel Formulas ascii, multiple lines, new line breaks, substitute, text to columns 6 Comments You’ve probably had to convert text to columns before in Excel. SPLIT is a built-in function in Excel VBA which can split the supplied sentence based on the delimiter. To do so, we’d highlight the first names in the wizard (notice the black highlight in the screenshot that signifies the active column) and then click the appropriate cell. Convert Text to Columns With Multiple Lines. We could do this differently-for example, adding first names to column B and last names to column C. Next, we’re going to click the cell where we want to start adding the data-in this case B2-and click “Finish.” This will add the first and last names to their respective columns. Though the condition of splitting a cell can be changed or customized by space between the words or by character between the words such as a comma depending upon the data. You can use any delimiter that fits your data set. In this case, we’re using comma and space because each cell in column A has a comma and a space separating the two. Delimiters are simply how the data is separated. Check both the “Comma” and “Space” delimiters and then the “Next” button.