Substitute Alphanumeric Values

[martinmc]

Hi, Is there any other alternative or shorter solution for rewriting the below formula example.i have read in an older post that the SUBSTITUTE function cannot handle the "old_text" part as an array{1,2,3,4,5,6,7,8,9,0}
Thanks In Advance.

[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD]A1B2C3D4E5F6G7H8J9K0
[/TD]
[TD]=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE( SUBSTITUTE(SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE(A1,0,""),1,""), 2,""),3,""),4,""), 5,""),6,""),7,""),8,""),9,"")
[/TD]
[/TR]
[TR]
[TD]Result In B1[/TD]
[TD][TABLE="width: 94"]
<tbody>[TR]
[TD="width: 94"]ABCDEFGHJK[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

And here is a non-looping one-liner UDF...

[table="width: 500"]
[tr]
	[td]Function TextOnly(ByVal S As String) As Variant
  TextOnly = Replace(Join(Evaluate("TRANSPOSE(IF(ISNUMBER(-MID(A1,ROW(1:" & Len(S) & "),1)),"""",MID(A1,ROW(1:" & Len(S) & "),1)))")), " ", "")
End Function[/td]
[/tr]
[/table]

 

[martinmc]

Thank You Sandy666, PowerQuery is all new to me at the moment and i will definitely give this a try out.

 

[martinmc]

Thank You Rick, not a big VBA user but i'm sure its another brilliant VBA answer.

 

[sandy666]

i am currently using Microsoft Office Home and Business 2013, Office 365 will be my next investment very soon.

Excel 2013 doesn't contain PowerQuery. Especially Excel H&B, so you really need higher version.

 

[martinmc]

Thank You Kweaver, will certainly give this a go when i get up to speed on VBA.

 

[martinmc]

Thanks all again for your time and replies, i am very new to this forum and i have heard a while back that there are very clever Excel people on this site and i have to say they are 100% correct so far.

 

[DanteAmor]

Thank You Dante Amor, not a big user of VBA yet but i will definitely give this a go.

Of course. Let me know if you have any doubt.