Extract all text without spaces and special characters
Glad I could help
"Alfredo_CPA" wrote:
Thanks Mike. That does the job perfectly!!! Yo made my day!!
I really appreciate your help!!
--
I'm not a looser, I keep trying…
"Mike H" wrote:
Hi,
A UDF will do it. Alt+F11 to open VB editor. Right click 'This Workbook' and
insert module and paste this in.
Function GetAlpha(target As Range)
Dim MyStr As String, i As Integer
MyStr = ""
If Len(target.Value) = 0 Then GoTo GoExit
For i = 1 To Len(target.Value)
If UCase(Mid(target, i, 1)) = Chr(65) And _
UCase(Mid(target, i, 1)) = Chr(90) _
Then MyStr = MyStr & Mid(target, i, 1)
Next i
GoExit:
GetAlpha = Left(MyStr, 7)
End Function
call with
=GetAlpha(a1)
Mike
"Alfredo_CPA" wrote:
I have an ugly vendor database (around 50,000 rows) from different sources
and the names of the vendors look like this example:
ABC Company
A B C Company
A.B.C. Company
ABC Co.
Meridian Inc
Meridian Incorporation
I’m thinking in a formula that extracts all but the spaces and special
characters, in that way my database will look like this:
ABCCompany
ABCCompany
ABCCompany
ABCCo
MeridianInc
MeridianIncorporation
Then I can put a formula like =Left(A1,5) and that will give me common names
as:
ABCCo
ABCCo
ABCCo
ABCCo
Merid
Merid
My two questions a
1. How do I extract all but spaces and special characters?
2. I really want to extract around 7 characters and don’t receive an error
if the numbers of characters are less than 7. How can I do that?
Thanks for your help!!
|