However, what if the data is inconsistent, and there is no comma or space delimiter that can be used? This can make separating the text in a string a little more difficult. One way to handle this is to use the Mid and Search functions.
For example, you can use the Mid and Search functions to separate some data into separate columns like this:
String Data Separated into 3 Columns
The MID function will extract data from the middle of a string. The syntax of the Mid and Search functions look like this:
MID(text, start_num, num_chars)
- Text is the string containing the characters to be extracted.
- Start_num is the position of the first character to be extracted.
- Num_chars specifies the number of characters to be returned from the text.
- Find_text is the text you want to find.
- Within_text is the text to be searched.
- Start_num (optional) is the character number at which you want to start searching. (Default is 1).
Assuming that the string data begins in cell B2, here is what the first function to extract the Section # would look like:
(Column 1 extracted) =MID(B2,SEARCH(” Sec*”,B2)+1,7)
- We are using an asterisk wildcard to find any character after ” Sec” which would include any punctuation or spaces after “Sec”. The +1 tells Excel to begin extracting the second character in quotes, which ignores the beginning space. The 7 tells Excel to extract 7 characters beginning with the “S” in “Sec.”
(Column 2 extracted) =MID(B2,SEARCH(” T*”,B2)+1,5)
- Here we are extracting the “T” string beginning with the “T” and extracting 5 characters.
(Column 3 extracted) =MID(B2,SEARCH(” R*”,B2)+1,4)
- Here we are extracting 4 characters beginning with the “R” string.
Once you have the data separated into columns, you can copy and paste this text as values (by using the paste and special command). Then you can perform a Find and Replace on the individual columns where necessary to remove unnecessary commas or periods, or to place zeros before numbers for consistency.