I just want to select the first word...
 

This should be easy but I can't find a function in the online help to
accomplish this in Excel so your help would be appreciated:

I have a long list of customer names. Sometimes the same customer is
entered under slightly different names. For example, a customer may
appear as Starline Inc. in one place and Starline Publishing Inc. in
another.

I am writing a VB script that will scan through the column, take the
first word (up to the first space) and then use that to compare to
other similar selections to create a list of suspected matches that
need to be cleaned up.

My problem is that I can't find or think of a quick way to "select" the
first word in order to assign it to a variable. I'm sure it is
something simple that I am simply overlooking in the online help.

Thanks in advance for all your collective guruness.

Nikko

I just want to select the first word...
 

Dim iPos As Integer
Dim str1 As String

str1 = Sheets("Sheet1").Range("A1")
iPos = InStr(str1, " ")
if iPos 0 then str1 = Left(str1, iPos - 1)

I just want to select the first word...
 

One way:

Dim vArr As Variant
Dim sTemp As String
Dim nPos As Long
Dim i As Long
vArr = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Value
For i = 1 To UBound(vArr, 1)
sTemp = Trim(vArr(i, 1))
nPos = InStr(sTemp, " ")
If nPos Then
vArr(i, 1) = Left(sTemp, nPos - 1)
Else
vArr(i, 1) = sTemp
End If
Next i


In article
.com,
"Nikko963" wrote:

This should be easy but I can't find a function in the online help to
accomplish this in Excel so your help would be appreciated:

I have a long list of customer names. Sometimes the same customer is
entered under slightly different names. For example, a customer may
appear as Starline Inc. in one place and Starline Publishing Inc. in
another.

I am writing a VB script that will scan through the column, take the
first word (up to the first space) and then use that to compare to
other similar selections to create a list of suspected matches that
need to be cleaned up.

My problem is that I can't find or think of a quick way to "select" the
first word in order to assign it to a variable. I'm sure it is
something simple that I am simply overlooking in the online help.

Thanks in advance for all your collective guruness.

Nikko

I just want to select the first word...
 

I always like:

Dim FirstWord as string
FirstWord=Split(cell.value," ")(0)

NickHK

"Nikko963" wrote in message
oups.com...
This should be easy but I can't find a function in the online help to
accomplish this in Excel so your help would be appreciated:

I have a long list of customer names. Sometimes the same customer is
entered under slightly different names. For example, a customer may
appear as Starline Inc. in one place and Starline Publishing Inc. in
another.

I am writing a VB script that will scan through the column, take the
first word (up to the first space) and then use that to compare to
other similar selections to create a list of suspected matches that
need to be cleaned up.

My problem is that I can't find or think of a quick way to "select" the
first word in order to assign it to a variable. I'm sure it is
something simple that I am simply overlooking in the online help.

Thanks in advance for all your collective guruness.

Nikko