Manipulating text data

S

saturnin02

Win XP HE, XL 2002

Hi,
I have a list of 1000 company names that I need to parse in this manner:

In Cell A1 abcmanagement should be in Cell A2 ABC Management
In A2 abcdefcompany should be in B2 ABCDEF Company
etc.....

There are a list of company with the following keywords: management,
company, firm, llc, that may appear in the names in cell in the A column.

What I need to do is a) insert a blank space between whatever is BEFORE the
above key words.

How to use a formula to quickly "parse" cells in the A column to insert this
blank space at the right place and separate the names as mentioned above for
the B column?

Tx for your help.

S
 
M

Myrna Larson

You talk about simply adding a space, but in comparing the original text with
what you say you want as output, that's not the only difference: you have also
changed the case of the first part (abcde to ABCDE, etc).

The following VBA function adds the space. It doesn't do anything about the
case. It does handle a company name that includes more than one of your target
strings, e.g. abccompanyllc


In B2, use the formula =AddSpace(A2)

Function AddSpace(CompName As String) AS String
Dim Words As Variant
Dim sTemp As String
Dim i As Long

Words = Array("Management","Company","Firm","LLC")
sTemp = CompName
For i = lbound(Words) to UBound(words)
sTemp = Replace(sTemp, Words(i), " " & Words(i))
Next i
AddSpace = Application.Trim(sTemp)
End Function

If this is a one-time job, and you don't want formulas, you could also used
Search and Replace to replace "company" with " Company", etc.
 
S

saturnin02

Myrna,
Tx for your suggestion and code.
You are right about the case--I do want to use the first letter
capitalization of the separate words but I didn't want to confuse the issue
in my post.
I figured I could always use the "Proper" function once it was parsed.
and cut and paste. Not as good as VBA Code of course....

I'll definitely try the code as it is not a 1 time job.
How many of the key words can I insert in the follwing line (I mean is there
a limit)?
Words = Array("Management","Company","Firm","LLC")

S
 
Top