Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
extract word before / | Excel Discussion (Misc queries) | |||
Extract first word for a highlighted selection (non contiguoes) | Excel Programming | |||
Splitting up one's highlighted selection as if you had used to"control" button for each selection | Excel Programming | |||
How to extract right-most word? | Excel Worksheet Functions | |||
Tools in excell,word & access not highlighted | Excel Discussion (Misc queries) |