How to Split a Text With Specific Character in Excel

Excel is a significant and powerful software program used for storing and analyzing data. The data in an excel worksheet is a combination of different texts and characters. The data consisting of text present in a cell is mostly separated with specific character known as delimiters. These characters include space, comma, hyphen, semicolon etc. Separation of text with these characters within a single cell makes it easy for the excel users to comprehend and interpret the data. But splitting of text with these specific characters is a difficult task.

For ease of its users excel consists of several functions that would help the excel users to split text with specific character. Splitting of text without any special characters can be done manually. But for separation of text consisting of specific characters, certain methods and techniques are required. In this article we will describe some important and easy techniques that would help the readers in understanding the concept of splitting of text with specific character in excel. These methods are compatible to 2016/2019/mac/online versions.

How To Split A Text With Specific Character In Excel Using LEFT Function?

A text in an excel spreadsheet contains different characters that divide the text in three components i.e., left, right and middle component. This function consists of formula which helps in the splitting of left component of text with specific character. The steps for accessing this feature are as follows. This method works with 2016/2019/mac/online versions.

1. First, we will select the cell containing the text with specific character that we want to split.

2. Then we will go to formula box and enter the formula =LEFT(A3,FIND(,,A3)-1) to split the left component of the string present in the selected cell.

3. The result will be splitting of first or left component of the selected string containing specific character.

This process is applicable for 2016/2019/mac/online versions.

How To Split A Text With Specific Character In Excel Using RIGHT Function?

For splitting the right component of a string with specific character in a cell we can use RIGHT Function. Like the LEFT Function the RIGHT Function also consists of formula that helps the excel users in splitting the end or the right component of the text. The steps of this technique are explained below. This method works with 2016/2019/mac/online versions.

1. First, we will select the substring with specific character that we want to split.

2. Then in formula box we will add formula =RIGHT(A3,LEN(A3)-FIND(,,A3,FIND(,,A3)+1)).

3. This will result in splitting of end of the text.

This process is applicable for 2016/2019/mac/online versions.

How To Split A Text With Specific Character In Excel Using MID Function?

Some of the text string consists of a middle component. For splitting the middle part of text with specific character we will follow the given steps. This function works with 2016/2019/mac/online versions.

1. First, we will select the cell containing the text with specific character

2. Then for the separation of middle segment of text we will apply the formula=MID(A3, FIND(,,A3) + 1, FIND(,,A3,FIND(,,A3)+1) - FIND(,,A3) - 1).

3. This will cause the separation of middle part of the text.

This method is applicable for 2016/2019/mac/online versions.

How To Split A String By Newline In Excel?

To split a string by newline in Excel, we will use the previous example. We will apply LEFT, RIGHT, and MID functions and additional “CHAR” functions.

Excel split string by character function

Let's suppose we have the following string separated by newlines in cell A2 :

Cristine Alliguay

42

New York

We will use the CHAR function in our previous commands to split these strings from our desired columns. Here are the commands to get the desired results :

To extract the Customer name:

=LEFT(A2, SEARCH(CHAR(10),A2,1)-1)

To extract the Customer City:

=RIGHT(A2,LEN(A2) - SEARCH(CHAR(10), A2, SEARCH(CHAR(10), A2) + 1))

To extract the Customer ID::

=MID(A2, SEARCH(CHAR(10),A2) + 1,SEARCH(CHAR(10),A2,SEARCH(CHAR(10),A2)+1) - SEARCH(CHAR(10),A2) - 1)

If you have multiple lines in the original string, the result will spill down the cells automatically. You can also copy the formula and apply it to other cells, as mentioned in the Screenshot. Simply copy (Ctrl+c) B2, C2 and D2 and then paste (Ctrl+v) by highlighting B3 B4, C3 C4 and D3 D4.

The Best Office Software - WPS office

WPS Office is a comprehensive suite with the best features and tools for creating, editing, and managing documents, spreadsheets, and presentations. Here are some of the important elements of WPS Office Suite:

• Writer: WPS Writer is a powerful word processing tool that allows you to create and edit documents with various formatting options such as spell check, track changes, document encryption, templates, tables, headers, footers, etc.

• Spreadsheets: WPS Spreadsheets provides a fully-featured spreadsheet application that includes functions, formulas, charts, conditional formatting, data sorting and filtering for complex calculations, data analysis, and visualization.

• Presentation: WPS Presentation enables you to easily create dynamic and professional slideshows for designing, arranging, and delivering presentations.

• PDF Tools: WPS Office provides tools for working with PDF files, including converting documents to PDF, merging multiple PDFs, splitting PDFs, adding watermarks, and protecting PDFs with passwords.

• Templates: It provides various pre-designed templates for various document types, including resumes, invoices, business plans, calendars, and more. These templates can help you save time and create professional-looking documents quickly.

• Mobile Apps: Besides desktop applications, WPS Office offers mobile apps for Android and iOS devices free of cost, allowing you to work on your documents, spreadsheets, and presentations on the go.

These are just some of the notable features offered by WPS Office, making it a versatile and feature-rich office suite for personal and professional use.

FAQs About Excel Split String By Character

Q1: How do you cut a string before a character in Excel?

To cut a string before a character in Excel, we can use the combination of  LEFT and FIND functions as given below =LEFT(A3,FIND(,,A3)-1)

Q2: How do you split a character string?

To split a character string in WPS Excel, we can use the Text to Columns feature, similar to the one in Microsoft Excel. Here's how we can do it:

• Select the cell or range containing the character string we want to split.

• Go to the "Data" tab in the WPS Excel ribbon.

• Click on the "Text to Columns" option and choose the delimiter that separates the parts of the string. We can select the "Delimited" option and specify the delimiter in the next step i.e. Tab, Semicolon, Comma, Space, and Other options.

• WPS Excel will split the character string based on the specified delimiter and populate the split parts into separate cells or columns.

Q3: How do I extract text before a specific character?

We can use functions like LEFT, FIND, and LEN in Excel to extract text before a specific character.

Formula : RIGHT(cell,LEN(cell)-SEARCH("char", cell))

The ‘Cell’ keyword represents the position of the field in Excel (You can specify the Cell Number from where you want to extract a text in a worksheet)

Conclusion

We have provided a detailed guideline on splitting a string in Excel by character after using functions such as LEFT, RIGHT, and MID to split text based on specific characters such as commas or newlines. Additionally, now you can use WPS Office efficiently for data manipulation in spreadsheets compared to other Suits. WPS Office is available for free on its official website.