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]

 

[Aryatect]

Hi martinmc,

If you have Office 365 or excel 2016 latest updated version you would have TEXTJOIN function, then below would be a shorter version of formula:

=TEXTJOIN("",TRUE,MID(A1,{1,3,5,7,9,11,13,15,17,19},1))

 

[kweaver]

If you don't have TEXTJOIN available to you, this UDF will work.

Function TextOnly(pWorkRng As Range) As String
Dim xValue As String
Dim OutValue As String
xValue = pWorkRng.Value
For xIndex = 1 To VBA.Len(xValue)
    If Not VBA.IsNumeric(VBA.Mid(xValue, xIndex, 1)) Then
        OutValue = OutValue & VBA.Mid(xValue, xIndex, 1)
    End If
Next
TextOnly = OutValue
End Function

Another alternative is to install the ASAP Utilities and they have a text function that will handle the entire worksheet.

 

[sandy666]

Next option is PowerQuery (Get&Transform):

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]raw[/td][td][/td][td=bgcolor:#70AD47]Result[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]A1B2C3D4E5F6G7H8J9K0[/td][td][/td][td=bgcolor:#E2EFDA]ABCDEFGHJK[/td][/tr]
[/table]

[SIZE=1]// Table1
let
    C2R = List.Transform({48..57}, each Character.FromNumber(_)),
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Text = Table.AddColumn(Table.ExpandListColumn(Table.TransformColumns(Source, {{"raw", Splitter.SplitTextByRepeatedLengths(1), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "raw"), "Text", each Text.Trim([raw],C2R)),
    Result = Table.RenameColumns(Table.FromList(Table.ToColumns(Table.SelectColumns(Table.SelectRows(Text, each ([Text] <> "")),{"Text"})), Splitter.SplitByNothing(), null, null, ExtraValues.Error),{{"Column1", "Result"}}),
    Extract = Table.TransformColumns(Result, {"Result", each Text.Combine(List.Transform(_, Text.From)), type text})
in
    Extract[/SIZE]

 

[Rick Rothstein]

Here is another UDF (user defined function) that you can consider...

Function TextOnly(ByVal S As String, Optional LettersOnly As Boolean) As String
  Dim X As Long, Pattern As String
  If LettersOnly Then Pattern = "[!A-Za-z]" Else Pattern = "#"
  For X = 1 To Len(S)
    If Mid(S, X, 1) Like Pattern Then Mid(S, X) = " "
  Next
  TextOnly = Replace(S, " ", "")
End Function

This function has an optional argument that allows you to return all non-numeric characters, punctuation included (omit the second argument or pass in False) or only letters A to Z and a to z (pass in True).

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use TextOnly just like it was a built-in Excel function. For example,

All non-numeric characters
------------------------------------------
=TextOnly(A1)

or

=TextOnly(A1,FALSE)

Letters Only
------------------------------
=TextOnly(A1,TRUE)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[sandy666]

one more (shorter) option with PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Raw[/td][td=bgcolor:#70AD47]Text[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]A1B2C3D4E5F6G7H8J9K0[/td][td=bgcolor:#E2EFDA]ABCDEFGHJK[/td][/tr]

[tr=bgcolor:#FFFFFF][td]zxc34nbv55qwerty[/td][td]zxcnbvqwerty[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]AaBb12345CcDdEe678UuRr900[/td][td=bgcolor:#E2EFDA]AaBbCcDdEeUuRr[/td][/tr]
[/table]

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Text = Table.AddColumn(Source, "Text", each Text.Combine(List.RemoveItems(Text.ToList([Raw]),{"0".."9"})))
in
    Text[/SIZE]

 

[Aryatect]

Well, 1 more option for formula:

=CONCAT(MID(A1,{1,3,5,7,9,11,13,15,17,19},1))

 

[DanteAmor]

Here is another UDF

[/COLOR]Function Subs_s(s As String)
    For i = 0 To 9
        s = Replace(s, i, "")
    Next
    Subs_s = s
End Function
[COLOR=#333333]

Use the udf as shown below

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:201.5px;" /><col style="width:163.49px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >A1B2C3D4E5F6G7H8J9K0</td><td >ABCDEFGHJK</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B1</td><td >=Subs_s(A1)</td></tr></table></td></tr></table>

 

[martinmc]

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

 

[martinmc]

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