Trying to extract text only from a alphanumeric string

Hello
A great evening to everyone seeing this post and a warm regards to all respected members.

I am trying to extract only text from a alphanumeric string without using substitute for 10 times.

for an example in A1
B4D5MAB3C12K789KLA0

in B1 i am expecting
BDMABCKKLA

I am trying to get it without using substitute function for around 10 times.

So far been able to generate locations of text and numbers as below
I am using below formula to generate locations of numbers
=MMULT(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=TRANSPOSE(CHAR(ROW(48:57)))),ROW(1:10))

i can use below to generate location of text
=NOT(MMULT(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=TRANSPOSE(CHAR(ROW(48:57)))),ROW(1:10)))*ROW(INDIRECT("1:"&LEN(A1)))

now stuck at how to pull values in a single cell from here is a bit of head cracking now.

Here is a simple way

Formula:

Please Login or Register  to view this content.

Excel 2016

	A	B
1	B4D5MAB3C12K789KLA0	BDMABCKKLA

Sheet: Sheet2

Edit: Sorry, I missed that part: "I am trying to get it without using substitute function for around 10 times".


The only other solution would be VBA

-----------------------------------------------------------------------------------------

1. Copy code below
2. Press Alt and F11 on your keyboard to open VB Editor
3. Click on Insert and select Module
4. Paste code into Module and close VB Editor.

Don't forget to save your worbook as Macro-Enabled workbook.

Please Login or Register  to view this content.

then use formula: =NumberOut(A1)

Excel 2016

	A	B
1	B4D5MAB3C12K789KLA0	BDMABCKKLA
2	AD65ADF65HHGF556FG0FGH	ADADFHHGFFGFGH
3	6WER00SDF456S1FSDF06548	WERSDFSFSDF

Sheet: Sheet1