Extract all text without spaces and special characters

[Alfredo_CPA]

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 are:
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!!

 

[Mike H]

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]

Thanks Mike. That does the job perfectly!!! Yo made my day!!
I really appreciate your help!!

 

[Mike H]

Glad I could help

Thanks Mike. That does the job perfectly!!! Yo made my day!!
I really appreciate your help!!

 

[Gord Dibben]

Alfredo

How about correcting the spelling in your sig?

The proper spelling is "loser"

"looser" means not as tight


Gord Dibben MS Excel MVP