Using the Excel TEXTJOIN function to Extract an Internal Numeric String From a String by David Hager

Before I had access to Excel 2016 (365), I made a comment on Chris Webb’s web site about a possible use for the new TEXTJOIN function.

https://blog.crossjoin.co.uk/2016/02/05/new-ways-to-concatenate-text-in-excel-2016-with-concat-and-textjoin/

Here is the comment:

… the following array formula will return a numeric string from a string (i.e. – 125 from AMDHF125KOI) by using this formula. =TEXTJOIN(“”,TRUE,IFERROR(VALUE(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)),””))

Chris confirmed that it worked!

Afterwards, I forgot about it, until I read the article on XOR LX’s web site about extracting numbers from a string.

https://excelxor.com/category/extracting-numbers-from-a-string/

In looking at the comparisons, I saw that this TEXTJOIN formula construction simplified the extraction of an internal numeric string from a string as compared to previous solutions.

I am looking at ways to extend this methodology to other string-to array-to string solutions.

Advertisements

One thought on “Using the Excel TEXTJOIN function to Extract an Internal Numeric String From a String by David Hager

  1. Pingback: Calculating Aggregation for Internal Numbers From Strings in a Range by David Hager | Excel For You

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s