Extract first word for a highlighted selection (non contiguoes)
On Wed, 3 Dec 2008 01:11:07 -0800 (PST), al wrote:
Can someone modify the macro below so that it works on a selection of
cells or give me an alternate macro.Thxs
Sub test()
Dim space As Integer, sen As String Dim fword As String
sen = ActiveCell.Value
space = InStr(1, sen, " ")
fword = Left(sen, space - 1)
ActiveCell.Value = fword
End Sub
I noted you also wanted to retain any leading zero's in numeric values, so try
this:
==============================
Option Explicit
Sub FWord()
Dim c As Range
For Each c In Selection
If Len(c.Text) 0 Then
c.NumberFormat = "@"
c.Value = Split(c.Text)(0)
End If
Next c
End Sub
===========================
If you want to put the first word in an adjacent cell, then:
===========================
Option Explicit
Sub FWord()
Dim c As Range
For Each c In Selection
With c.Offset(0, 1)
.ClearContents
.NumberFormat = "@"
If Len(c.Text) 0 Then
.Value = Split(c.Text)(0)
End If
End With
Next c
End Sub
=======================
--ron
|