Extract first word for a highlighted selection (non contiguoes)
 

On Dec 3, 1:36 pm, Mike H wrote:
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

"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

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

Extract first word for a highlighted selection (non contiguoes
 

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

"al" wrote:

On Dec 3, 1:36 pm, Mike H wrote:
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

"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

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

Extract first word for a highlighted selection (non contiguoes
 

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

"Mike H" wrote:

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

"al" wrote:

On Dec 3, 1:36 pm, Mike H wrote:
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

"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

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

Extract first word for a highlighted selection (non contiguoes)
 

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

On Dec 3, 10:10*am, al wrote:
On Dec 3, 1:36 pm, Mike H wrote:





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

"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

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

- Show quoted text -