Separate

This quick tutorial will demonstrate how to separate numbers after a symbol in Excel. In our example, we have a list of Serial numbers that were exported into an Excel file. By default, the serial numbers are formatted like “Serial#S988611.”

Clearly, this format is no ideal to be used in an Excel sheet. Therefore, we need to separate the actual numerical Serial number from the word Serial#.

This is achieved by using the following formula.

=RIGHT(A2,SEARCH("#",A2,1))

Let’s break down the formula. First we use the RIGHT Function to literally look for values to the right in Cell A2. Next, the SEARCH Function is tasked with looking for the “#” symbol. This can be modified to anything such as $, %, -, ^ etc. Once found, we tell Excel to strip 1 cell after the symbol has been identified. As a result, we are left with our Serial number.

Separate Numbers after a symbol