Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

Split text string at specific character

Figure 1. of Split Text Function in Excel.

By making use of the Excel Right, Mid, Left, LEN and SEARCH Functions, we are able to manipulate of data in form of text.

This post will help us illustrate how to use different combinations of the Right, Mid, SEARCH and LEN Functions to split a string of text at a specific character.

How to Split Text String by using Functions in Excel.

In the example illustrated below, we will be working with raw text string obtained from a clothing store. The storekeeper would like to have the data sorted and separated into different Excel sheet columns.

This can be done in 2 simple steps.

  1. Set up your Excel sheet and input the requirements for the data splitting.

Figure 2. of the Raw Text String.

  1. Our objective here is to split the Text String in the first column into three separate categories in our Excel sheet.

To extract ITEM from our RAW TEXT String, we will use the Left Function. This will separate all characters that appear before the first hyphen on the left side of the RAW TEXT String.

Generic Formula

=LEFT(text,FIND(character,text)-1)

  • We will enter into cell B2 is as follows =LEFT(A2,SEARCH("-",A2,1)-1)
  • This operation syntax should then be copied down the entire ITEM column.

Figure 3. of Left Function in Excel.

To extract COLOUR from our RAW TEXT String, will will use the Mid Function.

This will separate all characters that appear in the middle of the RAW TEXT String.

Generic Formula

=MID(text, FIND("-", text) + 1, FIND("-", text,FIND("-",text)+1) - FIND("-",text) - 1)

  • We will enter into cell C2 is as follows =MID(A2,SEARCH("-",A2)+1,SEARCH("-",A2, SEARCH("-",A2)+1)-SEARCH("-",A2)-1)
  • This operation syntax should then be copied down the entire COLOUR column

Figure 4. of the Mid Function in Excel.

To extract SIZE from our RAW TEXT String, we will use the Right Function.

This will separate all characters that appear after the second hyphen of the RAW TEXT String.

Generic Formula

=RIGHT(text,LEN(text) - FIND("-", text, FIND("-", text) + 1))

  • We will enter into cell D2 is as follows =RIGHT(A2,LEN(A2)-SEARCH("-",A2,SEARCH("-",A2)+1))
  • This operation syntax should then be copied down the entire SIZE column.

Figure 5. of the Right Function in Excel.

In the formulas and examples given above, the frequently occurring numbers -1 and +1 are corresponding with the number of characters in the Raw Text String.

In our illustration, it is a hyphen. 1 character. If our Raw Text String consisted of 2 characters, e.g. a space and a coma, then we would use only the comma (“,”) for the SEARCH function, and then use -2 and +2 rather than -1 and +1.

Figure 6. of Final Result.

Instant Connection to an Expert through our Excelchat Service

Our live Excelchat Service is here for you. We have Excel Experts available 24/7 to answer any Excel questions you may have. Guaranteed connection within 30 seconds and a customized solution for you within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Solution examples
Good morning, I need a cell formatting solution to display leading zero in my excel table with the following conditions. 1. Cannot format as text 2. cell size is not fixed. Please let me know if there is a solution.Thanks in advance
Solved by V. C. in 11 mins
looking for an Iferror formula that will bring back a blank value if a date is not listed. Currently using the following but want to add an Iferror statement: =TEXT(H2,"DDDD"). H2 is the raw date ex. 2/8/18. I tried =IFERROR(TEXT(H2,"DDDD"),"") but it keeps returning "saturday" instead of a blank cell. Any help is appreciated.
Solved by F. Q. in 20 mins
How do I make this formula =Value(IFerror(TEXT(AM3,"HMM"),"0")) Return a value of 2400 if the value is not an error?
Solved by F. J. in 21 mins
I have a column where each cell has letters and numbers. The numbers would range from 10 through 18. I would like to find a formula where I can search the cell for any one of the numbers and return a value from a different cell if it finds one. I currently search for a specific number and the formula works but only for that particular number. The formula I currently have is: =IF(ISNUMBER(SEARCH("16",F10)),G10,"") This does work if it finds the value of 16 in the (F10)cell and then places the value from cell "G10" as the result. If it doesn't find the numbers, it will leave the resultant cell blank.
Solved by O. H. in 60 mins
I have an excel spreadsheet with two worksheets. And the following formula is not pulling the data. =VLOOKUP($A$2:$A$566,'Module Type Info'!$A$2:$D$97,4,FALSE) In sheet 1, I am using all data in column 1 for the lookup In sheet 2, I have selected the first 4 columns of data for array I want to pull data from sheet 2, column 4 into sheet 1 Both tabs are sorted alphabetically. Confirmed that the value in column 4 of sheet 2 is a TEXT field. Why is this not pulling the info from sheet 2 into sheet 1
Solved by F. L. in 39 mins

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

Did this post not answer your question?
Get a solution from connecting with the expert

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc