Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I extract the only second word? | Excel Discussion (Misc queries) | |||
extract word before / | Excel Discussion (Misc queries) | |||
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) |