Extracting Letters and Numbers in Excel

• 2 mins read

If you haven’t used regex patterns below is a link to is an introductory article I wrote for the CPA Australia INTHEBLACK magazine – it includes a video.

https://intheblack.cpaaustralia.com.au/technical-skills/excel-tips-regex-arrives-in-excel

Extracting Numbers

If you want to extract all the numbers in a code the REGEXREPLACE function provides a solution – see the image below.

The formula in cell B2 (copied down) is:

=REGEXREPLACE(A2,"\D","")

The regex pattern \D means any character that is not a digit. This formula replaces all the non-digits with nothing.

Extracting Letters

The same process works for letters, but the regex pattern is longer – see image below.

The formula in cell D2 (copied down) is:

=REGEXREPLACE(A2,"[^A-Za-z]","")

Referring to letters is harder than numbers as uppercase and lowercase letters are treated separately in regex patterns. The regex pattern [^A-Za-z] means characters that are not letters of the alphabet. These are replaced with nothing, leaving just the letters.

REGEXEXTRACT returns the first matching text unless the pattern is designed to capture multiple matches. This means REGEXREPLACE is easier when you want to remove everything except the characters you need.

The REGEXREPLACE provides a simple way to remove unwanted characters from mixed codes and return only the numbers or letters you need.