Separate Numbers after a symbol

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

Leave a Reply

COUNTIF Function

The COUNTIF Function in Excel is extremely useful when dealing with large data sets. Here is a basic example of

Read More »

ROUND Function

The ROUND function in Excel returns a number rounded to a given number of digits. This can be used to

Read More »

DATEVALUE Function

The Excel DATEVALUE function is used to convert a date in Excel to text. =DATEVALUE (date_text) date_text – Date in

Read More »
Scroll to Top