Unleashing the Power of Excel Function - TEXT: A Formula Masterclass

Today's blog post is an enhanced version of our previous one.

https://www.excel-end.com/post/method-to-extract-numbers-from-alphanumeric-string

The requirement is to extract numbers from an alphanumeric string in a manner that is both efficient and easy to comprehend. This solution is sure to impress with its simplicity and effectiveness. Lot to learn, let's dive in.

The old formula is presented below.

=SUM(MID(0&B3,LARGE(ISNUMBER(MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1)*1)*ROW(INDIRECT("1:"&LEN(B3)))+1,ROW(INDIRECT("1:"&LEN(B3)))),1)*(10^(ROW(INDIRECT("1:"&LEN(B3)))-1)))

New Method:

The TEXT function allows us to accomplish the same task in a more efficient manner.

The TEXT function is essentially the formula approach to custom number formatting.

There are many ways to apply custom number formatting in a cell.

Basic format structure consists of four parts are shown below,

"Positive number; Negative number; Zero; Text"

Let me show you one simple example,

Select cells C16:C19 and press Ctrl + 1 to apply custom number formatting,

In Number tab, select the last option in category which is 'Custom' and enter

1;("Sorry");"Zero";"Name"

in type section and click OK.

This alteration in format serves solely as an illustrative example.

The actual value in the cell remains unchanged; only its appearance is altered.

Any positive number within a specified range will display as 1.

Any negative number within the specified range will be displayed as (Sorry).

Any 0 within a specified range will display as zero.

Any text within a specified range will display as Name.

if we ignore any of the four parts, then particular part will be shown as blank.

;("Sorry");"Zero";"Name"

We are going to use this type of format in TEXT function to do our task.

Formula in D3 cell is,

=CONCAT(TEXT(MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1),"0;;;"))

Let me start this from scratch,

Inner core of this function is

ROW(INDIRECT("1:"&LEN(B3)))

This will create sequence of numbers from 1 to length of B3 cell value which is 20,

Output of ROW, INDIRECT construction will look like below,

MID function helps us to pick the substring of main text,

Start_num argument helps us to feed the starting position of substring,

num_chars argument helps to feed the length of substring.

MID function extracts one character from each starting position of 1,2,3 and so on.

So, output of MID function looks like below,

In format text argument of TEXT function,

we feed four parts of format as we discussed earlier,

"0;;;"

We can use 0 or # symbol to show the positive number as it is in cell, we ignored remaining three parts. So, all parts other than positive numbers, will be shown as blanks.

Concatenating these texts will yield the following output:

Bonus Tip:

Below formula helps us to extract text from alphanumeric string,

=CONCAT(TEXT(MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1),";;;@"))

"@" symbol in text format portion helps us to keep the text as it is in cell.

Ignore all other three parts to ignore anything other than text.

The output following concatenation will appear as below,