How to split text string by comma or space in Excel
How to split text string by comma or space in Excel: Splitting a text string by comma or space is a common task when working with data. You may have a column that contains multiple pieces of information, and you need to sort and separate the data into different columns for various purposes. While you can do this manually for small datasets, it is impractical for larger spreadsheets. In this article, we will show you how to split a text string by comma or space in Excel.
Suppose you have a table of information as shown below, and you need to split the full names, genders, and birthdates into separate columns. To do this, follow the steps below.
How to split text string by comma or space in Excel
Step 1:
To split the full names, enter the following formula into cell C2 to extract the characters from the beginning up to the first comma in cell B2:
=LEFT(B2;FIND(",";B2;1)-1)
Copy the formula to the remaining cells in the full name column to obtain the separated full names.
How to split text string by comma or space in Excel
Step 2:
To split the genders, enter the following formula into cell D2 to extract the characters between the first and second commas in cell B2:
=MID(B2;SEARCH(",";B2;1)+1;SEARCH(",";B2;SEARCH(",";B2;1)+1) - SEARCH(",";B2;1)-1)
Copy the formula to the remaining cells in the gender column to obtain the separated genders.
How to split text string by comma or space in Excel
Step 3:
Finally, to split the birthdates, enter the following formula into cell E2 to extract the characters after the last comma in cell B2:
=RIGHT(B2;LEN(B2)-SEARCH(",";B2;SEARCH(",";B2;1)+1))
Copy the formula to the remaining cells in the birthdate column to obtain the separated birthdates.
How to split text string by comma or space in Excel
In conclusion, knowing how to split a text string into separate columns in Excel is a valuable skill for anyone working with data. Whether you are a business analyst, researcher, or student, being able to extract and manipulate data quickly and efficiently is essential. We hope this article has provided you with a useful guide to accomplishing this task, and we wish you success in all your data-related endeavors.
