Remove special characters and numbers

Sufiyan97 · Dec 22, 2020

I want to remove any numbers (0 to 9) and special texts like * # - ; X from below data

Book1

A

1

Description

2

CHECKCARD 1103 IN *J3 RESOURCES, 713-2900221 TX 55432860308200406920029 CKCD 7392 XXXXXXXXXXXX5599 XXXX XXXX XXXX 5599

3

CHECKCARD 1105 eBay O*02-06012-0 San Jose CA 15270210310001147494941 CKCD 5311 XXXXXXXXXXXX5599 XXXX XXXX XXXX 5599

4

CHECKCARD 1104 TX BD ENG LIC REN 5124403063 TX 55488720310091332000464 CKCD 9399 XXXXXXXXXXXX5599 XXXX XXXX XXXX 5599

5

CHECKCARD 1105 GILLMAN HONDA HOUSTON TX 55310200310207098200075 CKCD 5511 XXXXXXXXXXXX5599 XXXX XXXX XXXX 5599

6

CHECKCARD 1105 AMZN MKTP US*289V AMZN.COM/BILLWA 55310200311083703189089 CKCD 5942 XXXXXXXXXXXX5599 XXXX XXXX XXXX 5599

7

CHECKCARD 1107 TST* RUDY S COUNT SPRING TX 02305370312500225745329 CKCD 5812 XXXXXXXXXXXX5599 XXXX XXXX XXXX 5599

8

CHECKCARD 1106 AMZN MKTP US*287L AMZN.COM/BILLWA 55310200311083360221092 CKCD 5942 XXXXXXXXXXXX5599 XXXX XXXX XXXX 5599

Sheet1

NdNoviceHlp · Dec 22, 2020

This UDF will leave U with just letters... doesn't seem like that's exactly what U want?
Module code...

Code:

Public Function Answer(inputstr As String) As String
Dim newstr As String
' =Answer(A1)
newstr = vbNullString
'cap letters 65 to 90;
For i = 1 To Len(inputstr)
If Asc(Mid(inputstr, i, 1)) > 96 And Asc(Mid(inputstr, i, 1)) < 123 Then
newstr = newstr & Mid(inputstr, i, 1)
End If
'  small letters 97-122
If Asc(Mid(inputstr, i, 1)) > 64 And Asc(Mid(inputstr, i, 1)) < 91 Then
newstr = newstr & Mid(inputstr, i, 1)
End If
Next i
Answer = newstr
End Function

To operate, if your string is in A1, U would enter this in B1...

Code:

 =Answer(A1)

HTH. Dave

Sufiyan97 · Dec 22, 2020

NdNoviceHlp said:
This UDF will leave U with just letters... doesn't seem like that's exactly what U want?
Module code...

Code:

Public Function Answer(inputstr As String) As String Dim newstr As String ' =Answer(A1) newstr = vbNullString 'cap letters 65 to 90; For i = 1 To Len(inputstr) If Asc(Mid(inputstr, i, 1)) > 96 And Asc(Mid(inputstr, i, 1)) < 123 Then newstr = newstr & Mid(inputstr, i, 1) End If ' small letters 97-122 If Asc(Mid(inputstr, i, 1)) > 64 And Asc(Mid(inputstr, i, 1)) < 91 Then newstr = newstr & Mid(inputstr, i, 1) End If Next i Answer = newstr End Function

To operate, if your string is in A1, U would enter this in B1...

Code:

=Answer(A1)

HTH. Dave

Thank you
But I want to remove X also and space need to be there as it is, at least one space after each word

sandy666 · Dec 22, 2020

is that what you want?



Description		Custom
CHECKCARD 1103 IN *J3 RESOURCES, 713-2900221 TX 55432860308200406920029 CKCD 7392 XXXXXXXXXXXX5599 XXXX XXXX XXXX 5599		CHECKCARD IN J RESOURCES T CKCD
CHECKCARD 1105 eBay O*02-06012-0 San Jose CA 15270210310001147494941 CKCD 5311 XXXXXXXXXXXX5599 XXXX XXXX XXXX 5599		CHECKCARD eBay O San Jose CA CKCD
CHECKCARD 1104 TX BD ENG LIC REN 5124403063 TX 55488720310091332000464 CKCD 9399 XXXXXXXXXXXX5599 XXXX XXXX XXXX 5599		CHECKCARD T BD ENG LIC REN T CKCD
CHECKCARD 1105 GILLMAN HONDA HOUSTON TX 55310200310207098200075 CKCD 5511 XXXXXXXXXXXX5599 XXXX XXXX XXXX 5599		CHECKCARD GILLMAN HONDA HOUSTON T CKCD
CHECKCARD 1105 AMZN MKTP US*289V AMZN.COM/BILLWA 55310200311083703189089 CKCD 5942 XXXXXXXXXXXX5599 XXXX XXXX XXXX 5599		CHECKCARD AMZN MKTP USV AMZNCOMBILLWA CKCD
CHECKCARD 1107 TST* RUDY S COUNT SPRING TX 02305370312500225745329 CKCD 5812 XXXXXXXXXXXX5599 XXXX XXXX XXXX 5599		CHECKCARD TST RUDY S COUNT SPRING T CKCD
CHECKCARD 1106 AMZN MKTP US*287L AMZN.COM/BILLWA 55310200311083360221092 CKCD 5942 XXXXXXXXXXXX5599 XXXX XXXX XXXX 5599		CHECKCARD AMZN MKTP USL AMZNCOMBILLWA CKCD

NdNoviceHlp · Dec 22, 2020

Trial 2 doesn't seem like this is what U want either...

Code:

Public Function Answer(inputstr As String) As String
Dim newstr As String
' =Answer(A1)
newstr = vbNullString
'  small letters 97-122
For i = 1 To Len(inputstr)
If Asc(Mid(inputstr, i, 1)) > 96 And Asc(Mid(inputstr, i, 1)) < 123 Then
newstr = newstr & Mid(inputstr, i, 1)
End If
'cap letters 65 to 90;
If Asc(Mid(inputstr, i, 1)) > 64 And Asc(Mid(inputstr, i, 1)) < 91 Then
'remove cap "X"
If Asc(Mid(inputstr, i, 1)) <> 88 Then
newstr = newstr & Mid(inputstr, i, 1)
End If
End If
'keep spaces
If Asc(Mid(inputstr, i, 1)) = 32 Then
newstr = newstr & Mid(inputstr, i, 1)
End If
Next i
Answer = newstr
End Function

Dave

Sufiyan97 · Dec 22, 2020

sandy666 said:
is that what you want?

Description Custom
CHECKCARD 1103 IN *J3 RESOURCES, 713-2900221 TX 55432860308200406920029 CKCD 7392 XXXXXXXXXXXX5599 XXXX XXXX XXXX 5599 CHECKCARD IN J RESOURCES T CKCD
CHECKCARD 1105 eBay O*02-06012-0 San Jose CA 15270210310001147494941 CKCD 5311 XXXXXXXXXXXX5599 XXXX XXXX XXXX 5599 CHECKCARD eBay O San Jose CA CKCD
CHECKCARD 1104 TX BD ENG LIC REN 5124403063 TX 55488720310091332000464 CKCD 9399 XXXXXXXXXXXX5599 XXXX XXXX XXXX 5599 CHECKCARD T BD ENG LIC REN T CKCD
CHECKCARD 1105 GILLMAN HONDA HOUSTON TX 55310200310207098200075 CKCD 5511 XXXXXXXXXXXX5599 XXXX XXXX XXXX 5599 CHECKCARD GILLMAN HONDA HOUSTON T CKCD
CHECKCARD 1105 AMZN MKTP US*289V AMZN.COM/BILLWA 55310200311083703189089 CKCD 5942 XXXXXXXXXXXX5599 XXXX XXXX XXXX 5599 CHECKCARD AMZN MKTP USV AMZNCOMBILLWA CKCD
CHECKCARD 1107 TST* RUDY S COUNT SPRING TX 02305370312500225745329 CKCD 5812 XXXXXXXXXXXX5599 XXXX XXXX XXXX 5599 CHECKCARD TST RUDY S COUNT SPRING T CKCD
CHECKCARD 1106 AMZN MKTP US*287L AMZN.COM/BILLWA 55310200311083360221092 CKCD 5942 XXXXXXXXXXXX5599 XXXX XXXX XXXX 5599 CHECKCARD AMZN MKTP USL AMZNCOMBILLWA CKCD

Yes that is

Sufiyan97 · Dec 22, 2020

NdNoviceHlp said:

Trial 2 doesn't seem like this is what U want either...

Code:

Public Function Answer(inputstr As String) As String
Dim newstr As String
' =Answer(A1)
newstr = vbNullString
'  small letters 97-122
For i = 1 To Len(inputstr)
If Asc(Mid(inputstr, i, 1)) > 96 And Asc(Mid(inputstr, i, 1)) < 123 Then
newstr = newstr & Mid(inputstr, i, 1)
End If
'cap letters 65 to 90;
If Asc(Mid(inputstr, i, 1)) > 64 And Asc(Mid(inputstr, i, 1)) < 91 Then
'remove cap "X"
If Asc(Mid(inputstr, i, 1)) <> 88 Then
newstr = newstr & Mid(inputstr, i, 1)
End If
End If
'keep spaces
If Asc(Mid(inputstr, i, 1)) = 32 Then
newstr = newstr & Mid(inputstr, i, 1)
End If
Next i
Answer = newstr
End Function

Dave

Thank you very much NdNoviceHlp
Yes this is working perfectly

why you wrote
"doesn't seem like this is what U want either..."

sandy666 · Dec 22, 2020

alidurfani said:
Yes that is

so here is M for this

Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TS = Table.AddColumn(Source, "Custom", each Text.Select([Description], {"A".."W","Y","Z","a".."z"," "})),
    Index = Table.AddIndexColumn(TS, "Index", 1, 1),
    Split = Table.ExpandListColumn(Table.TransformColumns(Index, {{"Custom", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Custom"),
    Filter = Table.SelectRows(Split, each ([Custom] <> "")),
    Group = Table.Group(Filter, {"Index"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Custom", each [Count][Custom]),
    Extract = Table.TransformColumns(List, {"Custom", each Text.Combine(List.Transform(_, Text.From), " "), type text}),
    TSC = Table.SelectColumns(Extract,{"Custom"})
in
    TSC

Sufiyan97 · Dec 22, 2020

sandy666 said:

so here is M for this

Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TS = Table.AddColumn(Source, "Custom", each Text.Select([Description], {"A".."W","Y","Z","a".."z"," "})),
    Index = Table.AddIndexColumn(TS, "Index", 1, 1),
    Split = Table.ExpandListColumn(Table.TransformColumns(Index, {{"Custom", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Custom"),
    Filter = Table.SelectRows(Split, each ([Custom] <> "")),
    Group = Table.Group(Filter, {"Index"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Custom", each [Count][Custom]),
    Extract = Table.TransformColumns(List, {"Custom", each Text.Combine(List.Transform(_, Text.From), " "), type text}),
    TSC = Table.SelectColumns(Extract,{"Custom"})
in
    TSC

Thank you very much sandy666

Power Query is always Great!

sandy666 · Dec 22, 2020

You are welcome & thanks for the feedback

alidurfani said:
Power Query is always Great!

You've BIG right

Remove special characters and numbers

Sufiyan97

Well-known Member

Excel Facts

NdNoviceHlp

Well-known Member

Sufiyan97

Well-known Member

sandy666

Banned - Rules violations

NdNoviceHlp

Well-known Member

Sufiyan97

Well-known Member

Sufiyan97

Well-known Member

sandy666

Banned - Rules violations

Sufiyan97

Well-known Member

sandy666

Banned - Rules violations

Similar threads

Forum statistics

Share this page

Remove special characters and numbers

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Banned - Rules violations

Well-known Member

Well-known Member

Well-known Member

Banned - Rules violations

Well-known Member

Banned - Rules violations

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock