Remove special characters and numbers

Sufiyan97

Sufiyan97

Well-known Member
Joined
Apr 12, 2019
Messages
1,584
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
I want to remove any numbers (0 to 9) and special texts like * # - ; X from below data

Book1
A
1Description
2CHECKCARD 1103 IN *J3 RESOURCES, 713-2900221 TX 55432860308200406920029 CKCD 7392 XXXXXXXXXXXX5599 XXXX XXXX XXXX 5599
3CHECKCARD 1105 eBay O*02-06012-0 San Jose CA 15270210310001147494941 CKCD 5311 XXXXXXXXXXXX5599 XXXX XXXX XXXX 5599
4CHECKCARD 1104 TX BD ENG LIC REN 5124403063 TX 55488720310091332000464 CKCD 9399 XXXXXXXXXXXX5599 XXXX XXXX XXXX 5599
5CHECKCARD 1105 GILLMAN HONDA HOUSTON TX 55310200310207098200075 CKCD 5511 XXXXXXXXXXXX5599 XXXX XXXX XXXX 5599
6CHECKCARD 1105 AMZN MKTP US*289V AMZN.COM/BILLWA 55310200311083703189089 CKCD 5942 XXXXXXXXXXXX5599 XXXX XXXX XXXX 5599
7CHECKCARD 1107 TST* RUDY S COUNT SPRING TX 02305370312500225745329 CKCD 5812 XXXXXXXXXXXX5599 XXXX XXXX XXXX 5599
8CHECKCARD 1106 AMZN MKTP US*287L AMZN.COM/BILLWA 55310200311083360221092 CKCD 5942 XXXXXXXXXXXX5599 XXXX XXXX XXXX 5599
Sheet1
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
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
 
Upvote 0
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
Click to expand...
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
 
Upvote 0
is that what you want?
DescriptionCustom
CHECKCARD 1103 IN *J3 RESOURCES, 713-2900221 TX 55432860308200406920029 CKCD 7392 XXXXXXXXXXXX5599 XXXX XXXX XXXX 5599CHECKCARD IN J RESOURCES T CKCD
CHECKCARD 1105 eBay O*02-06012-0 San Jose CA 15270210310001147494941 CKCD 5311 XXXXXXXXXXXX5599 XXXX XXXX XXXX 5599CHECKCARD eBay O San Jose CA CKCD
CHECKCARD 1104 TX BD ENG LIC REN 5124403063 TX 55488720310091332000464 CKCD 9399 XXXXXXXXXXXX5599 XXXX XXXX XXXX 5599CHECKCARD T BD ENG LIC REN T CKCD
CHECKCARD 1105 GILLMAN HONDA HOUSTON TX 55310200310207098200075 CKCD 5511 XXXXXXXXXXXX5599 XXXX XXXX XXXX 5599CHECKCARD GILLMAN HONDA HOUSTON T CKCD
CHECKCARD 1105 AMZN MKTP US*289V AMZN.COM/BILLWA 55310200311083703189089 CKCD 5942 XXXXXXXXXXXX5599 XXXX XXXX XXXX 5599CHECKCARD AMZN MKTP USV AMZNCOMBILLWA CKCD
CHECKCARD 1107 TST* RUDY S COUNT SPRING TX 02305370312500225745329 CKCD 5812 XXXXXXXXXXXX5599 XXXX XXXX XXXX 5599CHECKCARD TST RUDY S COUNT SPRING T CKCD
CHECKCARD 1106 AMZN MKTP US*287L AMZN.COM/BILLWA 55310200311083360221092 CKCD 5942 XXXXXXXXXXXX5599 XXXX XXXX XXXX 5599CHECKCARD AMZN MKTP USL AMZNCOMBILLWA CKCD
 
Upvote 0
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
 
Upvote 0
Solution
sandy666 said:
is that what you want?
DescriptionCustom
CHECKCARD 1103 IN *J3 RESOURCES, 713-2900221 TX 55432860308200406920029 CKCD 7392 XXXXXXXXXXXX5599 XXXX XXXX XXXX 5599CHECKCARD IN J RESOURCES T CKCD
CHECKCARD 1105 eBay O*02-06012-0 San Jose CA 15270210310001147494941 CKCD 5311 XXXXXXXXXXXX5599 XXXX XXXX XXXX 5599CHECKCARD eBay O San Jose CA CKCD
CHECKCARD 1104 TX BD ENG LIC REN 5124403063 TX 55488720310091332000464 CKCD 9399 XXXXXXXXXXXX5599 XXXX XXXX XXXX 5599CHECKCARD T BD ENG LIC REN T CKCD
CHECKCARD 1105 GILLMAN HONDA HOUSTON TX 55310200310207098200075 CKCD 5511 XXXXXXXXXXXX5599 XXXX XXXX XXXX 5599CHECKCARD GILLMAN HONDA HOUSTON T CKCD
CHECKCARD 1105 AMZN MKTP US*289V AMZN.COM/BILLWA 55310200311083703189089 CKCD 5942 XXXXXXXXXXXX5599 XXXX XXXX XXXX 5599CHECKCARD AMZN MKTP USV AMZNCOMBILLWA CKCD
CHECKCARD 1107 TST* RUDY S COUNT SPRING TX 02305370312500225745329 CKCD 5812 XXXXXXXXXXXX5599 XXXX XXXX XXXX 5599CHECKCARD TST RUDY S COUNT SPRING T CKCD
CHECKCARD 1106 AMZN MKTP US*287L AMZN.COM/BILLWA 55310200311083360221092 CKCD 5942 XXXXXXXXXXXX5599 XXXX XXXX XXXX 5599CHECKCARD AMZN MKTP USL AMZNCOMBILLWA CKCD
Click to expand...
Yes that is
 
Upvote 0
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
Click to expand...
Thank you very much NdNoviceHlp
Yes this is working perfectly

why you wrote
"doesn't seem like this is what U want either..."
 
Upvote 0
alidurfani said:
Yes that is
Click to expand...
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
 
Upvote 0
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
Click to expand...
Thank you very much sandy666

Power Query is always Great!
 
Upvote 0
You must log in or register to reply here.

Similar threads

Sufiyan97
  • Locked
  • Question Question
Lookup Partial Text
Replies
1
Views
907
Fluff
Fluff
Sufiyan97
  • Question Question
Partial Text Lookup
2
Replies
18
Views
2K
Sufiyan97
Sufiyan97

Forum statistics

Threads
1,223,248
Messages
6,171,021
Members
452,374
Latest member
keccles

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top