How To Remove Part Of A String

In the contempo couple of articles, nosotros've looked at different ways to remove characters from strings in Excel. Today, we'll investigate 1 more utilize case - how to delete everything earlier or after a specific character.

• Delete text with Find and Supplant
• Remove part of text with Flash Fill up
• Remove text using formulas
  • Remove everything after a grapheme
  • Delete everything before a character
  • Strip text after Nth occurrence of a graphic symbol
  • Strip text earlier Nth occurrence of a character
  • Remove everything after the last occurrence of a character
  • Remove everything earlier the last occurrence of a character
  • Custom function to remove text on either side of a character
• Remove substrings with Ultimate Suite

Delete text before, after or between 2 characters with Find & Replace

For data manipulations in multiple cells, Discover and Supercede is the right tool. To remove part of a string preceding or following a specific character, these are the steps to perform:

1. Select all the cells where y'all want to delete text.
2. Press Ctrl + H to open the Find and Replace dialog.
3. In the Find what box, enter one of the following combinations:
   • To eliminate text before a given character, type the character preceded by an asterisk (*char).
   • To remove text afterwards a sure grapheme, type the character followed by an asterisk (char*).
   • To delete a substring between 2 characters, type an asterisk surrounded by 2 characters (char*char).
4. Go out the Replace with box empty.
5. Click Supercede all.

For instance, to remove everything afterward a comma including the comma itself, put a comma and an asterisk sign (,*) in the Notice what box, and you'll become the post-obit event:

To delete a substring before a comma, type an asterisk, a comma, and a infinite (*, ) in the Find what box.

Please notice that we are replacing not just a comma only a comma and a infinite to prevent leading spaces in the results. If your data is separated by commas without spaces, and then apply an asterisk followed by a comma (*,).

To delete text betwixt 2 commas, use an asterisk surrounded by commas (,*,).

Tip. If you'd rather accept the names and phone numbers separated by a comma, then blazon a comma (,) in the Replace with field.

Remove part of text using Flash Make full

In mod versions of Excel (2013 and later on), in that location is i more piece of cake way to eradicate text that precedes or follows a specific character - the Flash Fill feature. Here'south how it works:

1. In a cell side by side to the outset cell with your data, type the expected result and press Enter.
2. Offset typing an appropriate value in the next jail cell. Once Excel feels the pattern in the values you lot are entering, information technology will display a preview for the remaining cells following the same pattern.
3. Hit the Enter key to have the suggestion.

Done!

Remove text using formulas

In Microsoft Excel, many data manipulations performed by using inbuilt features can also exist accomplished with a formula. Unlike the previous methods, formulas practice not make any changes to the original information and give y'all more than command over the results.

How to remove everything afterward a specific character

To delete text after a particular grapheme, the generic formula is:

LEFT(cell, SEARCH("char", prison cell) -1)

Here, we utilize the SEARCH function to get the position of the grapheme and pass it to the LEFT function, and then it extracts the corresponding number of characters from the start of the cord. One character is subtracted from the number returned by SEARCH to exclude the delimiter from the results.

For example, to remove office of a string after a comma, yous enter the below formula in B2 and drag it downward through B7:

=LEFT(A2, SEARCH(",", A2) -1)

How to remove everything before a specific character

To delete part of a text string before a certain graphic symbol, the generic formula is:

RIGHT(cell, LEN(jail cell) - SEARCH("char", cell))

Here, we again calculate the position of the target character with the aid of SEARCH, decrease it from the total cord length returned by LEN, and pass the difference to the RIGHT function, so information technology pulls that many characters from the end of the cord.

For example, to remove text before a comma, the formula is:

=Right(A2, LEN(A2) - SEARCH(",", A2))

In our instance, the comma is followed by a space grapheme. To avert leading spaces in the results, we wrap the core formula in the TRIM function:

=TRIM(Correct(A2, LEN(A2) - SEARCH(",", A2)))

Notes:

• Both of the above examples assume that there is only one instance of the delimiter in the original string. If in that location are multiple occurrences, text will be removed earlier/after the first case.
• The SEARCH function is not case-sensitive, meaning it makes no departure between lowercase and capital characters. If your specific character is a letter and you want to distinguish the alphabetic character case, then use the instance-sensitive Find function instead of SEARCH.

How to delete text after Nth occurrence of a character

In state of affairs when a source cord contains multiple instances of the delimiter, you may accept a need to remove text after a specific example. For this, apply the post-obit formula:

LEFT(cell, FIND("#", SUBSTITUTE(prison cell, "char", "#", n)) -one)

Where n is the graphic symbol'south occurrence after which to remove text.

The internal logic of this formula requires using some character that is not nowadays anywhere in the source information, a hash symbol (#) in our case. If this character occurs in your data set, then apply something else instead of "#".

For example, to remove everything after the ii^nd comma in A2 (and the comma itself), the formula is:

=LEFT(A2, FIND("#", SUBSTITUTE(A2, ",", "#", 2)) -1)

How this formula works:

The central part of the formula is the Observe function that calculates the position of the nth delimiter (comma in our example). Here's how:

We replace the ii^nd comma in A2 with a hash symbol (or whatsoever other character that does non exist in your information) with the aid of SUBSTITUTE:

SUBSTITUTE(A2, ",", "#", ii)

The resulting string goes to the 2^nd argument of Detect, so it finds the position of "#" in that string:

Find("#", "Emma, Pattern# (102) 123-4568")

FIND tells us that "#" is the 13^th character in the cord. To know the number of characters preceding information technology, just decrease 1, and you'll go 12 as the result:

FIND("#", SUBSTITUTE(A2, ",", "#", 2)) - one

This number goes straight to the num_chars argument of LEFT asking it to pull the first 12 characters from A2:

=LEFT(A2, 12)

That's it!

How to delete text earlier Nth occurrence of a character

The generic formula to remove a substring before a certain graphic symbol is:

RIGHT(SUBSTITUTE(prison cell, "char", "#", n), LEN(cell) - FIND("#", SUBSTITUTE(cell, "char", "#", n)) -1)

For example, to strip off text earlier the two^nd comma in A2, the formula is:

=RIGHT(SUBSTITUTE(A2, ",", "#", ii), LEN(A2) - FIND("#", SUBSTITUTE(A2, ",", "#", 2)) -1)

To eliminate a leading space, we once more use the TRIM function as a wrapper:

=TRIM(Correct(SUBSTITUTE(A2, ",", "#", 2), LEN(A2) - Detect("#", SUBSTITUTE(A2, ",", "#", ii))))

How this formula works:

In summary, nosotros find out how many characters are after the nth delimiter and extract a substring of the corresponding length from right. Below is the formula break down:

Commencement, we supercede the two^nd comma in A2 with a hash symbol:

SUBSTITUTE(A2, ",", "#", 2)

The resulting cord goes to the text statement of RIGHT:

RIGHT("Emma, Design# (102) 123-4568", …

Next, we need to define how many characters to extract from the end of the string. For this, we observe the position of the hash symbol in the above string (which is 13):

Detect("#", SUBSTITUTE(A2, ",", "#", two))

And subtract it from the full string length (which equals to 28):

LEN(A2) - FIND("#", SUBSTITUTE(A2, ",", "#", two))

The difference (15) goes to the second argument of Right instructing it to pull the last xv characters from the string in the beginning statement:

RIGHT("Emma, Design# (102) 123-4568", 15)

The output is a substring " (102) 123-4568", which is very close to the desired upshot, except a leading space. And so, we use the TRIM office to get rid of it.

How to remove text after the last occurrence of a character

In case your values are separated with a variable number of delimiters, you may want to remove everything later the last instance of that delimiter. This can be done with the following formula:

LEFT(cell, FIND("#", SUBSTITUTE(jail cell, "char", "#", LEN(cell) - LEN(SUBSTITUTE(cell, "char ", "")))) -1)

Suppose cavalcade A contains various information almost employees, but the value after the final comma is always a phone number. Your goal is to remove phone numbers and keep all other details.

To attain the goal, you lot can remove text after the last comma in A2 with this formula:

=LEFT(A2, Discover("#", SUBSTITUTE(A2, ",", "#", LEN(A2) - LEN(SUBSTITUTE(A2, ",","")))) -one)

Copy the formula down the column, and you lot'll get this issue:

How this formula works:

The gist of the formula is that we determine the position of the last delimiter (comma) in the string and pull a substring from left up to the delimiter. Getting the delimiter'south position is the trickiest part, and hither's how nosotros handle information technology:

First, we observe out how many commas there are in the original string. For this, nosotros replace each comma with nothing ("") and serve the resulting string to the LEN function:

LEN(SUBSTITUTE(A2, ",",""))

For A2, the outcome is 35, which is the number of characters in A2 without commas.

Subtract the above number from the total string length (38 characters):

LEN(A2) - LEN(SUBSTITUTE(A2, ",",""))

… and y'all will go 3, which is the total number of commas in A2 (and as well the ordinal number of the last comma).

Next, y'all utilize the already familiar combination of the FIND and SUBSTITUTE functions to go the position of the last comma in the string. The instance number (iii^rd comma in our case) is supplied by the above-mentioned LEN SUBSTITUTE formula:

FIND("#", SUBSTITUTE(A2, ",", "#", three))

Information technology appears that the 3^rd comma is the 23^rd grapheme in A2, meaning we need to excerpt 22 characters preceding information technology. So, we put the above formula minus 1 in the num_chars argument of LEFT:

LEFT(A2, 23-1)

How to remove text before the last occurrence of a character

To delete everything before the concluding instance of a specific character, the generic formula is:

RIGHT(cell, LEN(cell) - Detect("#", SUBSTITUTE(cell, "char", "#", LEN(prison cell) - LEN(SUBSTITUTE(cell, "char", "")))))

In our sample table, to eradicate text earlier the last comma, the formula takes this form:

=RIGHT(A2, LEN(A2) - Detect("#", SUBSTITUTE(A2, ",", "#", LEN(A2) - LEN(SUBSTITUTE(A2, ",","")))))

Equally a finishing touch, we nest it into the TRIM part to eliminate leading spaces:

=TRIM(Right(A2, LEN(A2) - Observe("#", SUBSTITUTE(A2, ",", "#", LEN(A2) - LEN(SUBSTITUTE(A2, ",",""))))))

How this formula works:

In summary, we get the position of the final comma every bit explained in the previous example and subtract it from the full length of the string:

LEN(A2) - FIND("#", SUBSTITUTE(A2, ",", "#", LEN(A2) - LEN(SUBSTITUTE(A2, ",",""))))

As the upshot, we get the number of characters subsequently the concluding comma and pass it to the Right function, then it brings that many characters from the cease of the string.

Custom role to remove text on either side of a grapheme

As you lot have seen in the higher up examples, yous tin resolve almost any use case by using Excel'due south native functions in unlike combinations. The trouble is that you lot need to remember of handful of catchy formulas. Hmm, what if we write our own part to cover all the scenarios? Sounds like a adept idea. So, add the following VBA code to your workbook (the detailed steps to insert VBA in Excel are here):

Part RemoveText(str As Cord, delimiter As Cord, occurrence As Integer, is_after As Boolean) 	Dim delimiter_num, start_num, delimiter_len  As Integer 	Dim str_result Equally String  	delimiter_num = 0 	start_num = i 	str_result = "" 	delimiter_len = Len(delimiter)  	For i = i To occurrence 		delimiter_num = InStr(start_num, str, delimiter, vbTextCompare) 		If 0 < delimiter_num And so 			start_num = delimiter_num + delimiter_len 		End If 	Next i  	If 0 < delimiter_num And so 		If True = is_after And then 			str_result = Mid(str, one, start_num - delimiter_len - ane) 		Else 			str_result = Mid(str, start_num) 		End If 	Terminate If 	RemoveText = str_result End Function          

Our function is named RemoveText and it has the following syntax:

RemoveText(string, delimiter, occurrence, is_after)

Where:

String - is the original text string. Can exist represented by a cell reference.

Delimiter - the character before/later which to remove text.

Occurrence - the instance of the delimiter.

Is_after - a Boolean value that indicates on which side of the delimiter to remove text. Tin can be a unmarried character or a sequence of characters.

• TRUE - delete everything after the delimiter (including the delimiter itself).
• FALSE - delete everything before the delimiter (including the delimiter itself).

Once the function's code is inserted in your workbook, you tin remove substrings from cells using compact and elegant formulas.

For instance, to erase everything after the 1^st comma in A2, the formula in B2 is:

=RemoveText(A3, ", ", 1, TRUE)

To delete everything before the i^st comma in A2, the formula in C2 is:

=RemoveText(A3, ", ", 1, FALSE)

Since our custom function accepts a string for the delimiter, we put a comma and a space (", ") in the 2^nd statement to spare the trouble of trimming leading spaces later on.

Our custom function works beautifully, doesn't information technology? But if you think information technology's the comprehensive solution, you haven't seen the next example yet :)

Delete everything earlier, after or between characters

To go even more options for removing individual characters or text from multiple cells, by lucifer or position, add together our Ultimate Suite to your Excel toolbox.

Here, we'll take a closer wait at the Remove by Position feature located on the Ablebits Information tab > Text grouping > Remove.

Below, we will comprehend the two about common scenarios.

Remove everything before or afterward sure text

Suppose all your source strings contain some common word or text and you wish to delete everything before or after that text. To have information technology done, select your source information, run the Remove by Position tool, and configure information technology like shown below:

1. Select the All characters before text or All characters later text option and type the key text (or character) in the box next to information technology.
2. Depending on whether majuscule and lowercase letters should be treated every bit different or the same characters, check or uncheck the Case-sensitive box.
3. Hit Remove.

In this example, we are removing all characters preceding the word "mistake" in cells A2:A8:

And go exactly the result we are looking for:

Remove text between two characters

In situation when irrelevant data is between 2 specific characters, here's how you can quickly delete it:

1. Choose Remove all substrings and blazon ii characters in the below boxes.
2. If the "between" characters should exist removed too, check the Including delimiters box.
3. Click Remove.
   

As an instance, nosotros delete everything between two tilde characters (~), and get the perfectly cleaned strings equally the result:

To effort other useful features included with this multi-functional tool, I encourage yous to download an evaluation version at the cease of this post. Thank you for reading and promise to run across you lot on our web log adjacent calendar week!

Available downloads

Remove first or last characters - examples (.xlsm file)
Ultimate Suite - trial version (.zippo file)

You lot may as well be interested in

How To Remove Part Of A String,

Source: https://www.ablebits.com/office-addins-blog/remove-text-before-after-between-characters-excel/

Posted by: mckeehanhunty1966.blogspot.com

Share this post