Extract first word for a highlighted selection (non contiguoes)

[al]

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

 

[Mike H]

Hi,

Try this

Sub test()
On Error Resume Next
For Each sen In Selection
sen.Value = Left(sen.Value, InStr(1, sen.Value, " ") - 1)
Next
End Sub

Mike

 

[paul.robinson]

Hi
Public Function fword(CellValue As String) As String
Dim space As Integer
space = InStr(1, CellValue, " ")
fword = Left(CellValue, space - 1)
End Function

Sub NewTest()
Dim myRange As Range, Cell As Range
Set myRange = Selection
For Each Cell In myRange
Cell.Value = fword(Cell.Value)
Next Cell
Set myRange = Nothing
End Sub

regards
Paul

 

[al]

Hi,

Try this

Sub test()
On Error Resume Next
For Each sen In Selection
sen.Value = Left(sen.Value, InStr(1, sen.Value, " ") - 1)
Next
End Sub

Mike

Hi mike,
it's fine but a text like "0234 mike" gives 234 instead of 0234 - can
you help

 

[Mike H]

Try this

Sub test()
On Error Resume Next
For Each sen In Selection
With sen
.NumberFormat = "@"
.Value = Left(sen.Value, InStr(1, sen.Value, " ") - 1)
End With
Next
End Sub

Mike

 

[Mike H]

Hi,

I should have mentioned you may now get a small error warning triangle in
cells with leading zeroes warning of numbers stored as text. If you want to
get rid of it:-

Tools|Options - Error checking and un-check 'numbers stored as text'.

Mike

 

[paul.robinson]

Hi
Format your selection as text first, not general.
regards
Paul

 

[Ron Rosenfeld]

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