| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easy Question
I've got several rows of text as follows: Apples are nice - So are pears Monkeys are kind - So are dogs Earth is beautiful - So is mars I want to delete the space before the minus sign and everything after by way of a macro... So, all I want left is: Apples are nice Monkeys are kind Earth is beautiful Can someone help me with this? Thanks guys, I appreciate your time. -- holyearth ------------------------------------------------------------------------ holyearth's Profile: http://www.excelforum.com/member.php...o&userid=34022 View this thread: http://www.excelforum.com/showthread...hreadid=549103 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Easy Question
^Bump^ ^Bump^ -- holyearth ------------------------------------------------------------------------ holyearth's Profile: http://www.excelforum.com/member.php...o&userid=34022 View this thread: http://www.excelforum.com/showthread...hreadid=549103 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Easy Question
^Bumpetty Bump^ -- holyearth ------------------------------------------------------------------------ holyearth's Profile: http://www.excelforum.com/member.php...o&userid=34022 View this thread: http://www.excelforum.com/showthread...hreadid=549103 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Easy Question
Hi HolyEarth,
Try: '============= Public Sub Tester() Dim rng As Range Dim rCell As Range Dim pos As Long Set rng = ActiveSheet.Range("A1:A100") '<<==== CHANGE For Each rCell In rng.Cells With rCell pos = InStr(1, .Value, "-") If pos 0 Then .Value = Left(.Value, pos - 1) End If End With Next rCell End Sub '<<============= --- Regards, Norman "holyearth" wrote in message ... I've got several rows of text as follows: Apples are nice - So are pears Monkeys are kind - So are dogs Earth is beautiful - So is mars I want to delete the space before the minus sign and everything after by way of a macro... So, all I want left is: Apples are nice Monkeys are kind Earth is beautiful Can someone help me with this? Thanks guys, I appreciate your time. -- holyearth ------------------------------------------------------------------------ holyearth's Profile: http://www.excelforum.com/member.php...o&userid=34022 View this thread: http://www.excelforum.com/showthread...hreadid=549103 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Easy Question
Hi HolyEarth,
^Bump^ ^Bump^ A bump after six minutes!!! As a matter of interest, how quickly had you anticipated receiving a response? --- Regards, Norman |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Easy Question
Record a macro when you select your range
edit|replace what: _* (the underscore represents a space character) with: (leave blank) replace all holyearth wrote: I've got several rows of text as follows: Apples are nice - So are pears Monkeys are kind - So are dogs Earth is beautiful - So is mars I want to delete the space before the minus sign and everything after by way of a macro... So, all I want left is: Apples are nice Monkeys are kind Earth is beautiful Can someone help me with this? Thanks guys, I appreciate your time. -- holyearth ------------------------------------------------------------------------ holyearth's Profile: http://www.excelforum.com/member.php...o&userid=34022 View this thread: http://www.excelforum.com/showthread...hreadid=549103 -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Easy Question
Norman, That worked like a charm!! One last question, Say I have text like this: The world is a nice place (Yes it is) The world is a polluted place (It could be better) How can I modify your macro to delete the contents of the parenthesi and the parenthesis themselves? - To leave only: The world is a nice place The world is a polluted place Thanks Norman, I appreciate your time -- holyeart ----------------------------------------------------------------------- holyearth's Profile: http://www.excelforum.com/member.php...fo&userid=3402 View this thread: http://www.excelforum.com/showthread.php?threadid=54910 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Easy Question
Hi Dave, This didn't work to remove the ( ) and it's contents... Maybe I did it wrong -- holyeart ----------------------------------------------------------------------- holyearth's Profile: http://www.excelforum.com/member.php...fo&userid=3402 View this thread: http://www.excelforum.com/showthread.php?threadid=54910 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Easy Question
Hey Norman, Sorry but i'm over anxious and too high-strung :-) -- holyearth ------------------------------------------------------------------------ holyearth's Profile: http://www.excelforum.com/member.php...o&userid=34022 View this thread: http://www.excelforum.com/showthread...hreadid=549103 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Easy Question
Hey guys, this worked: '============= Public Sub Tester() Dim rng As Range Dim rCell As Range Dim pos As Long Set rng = ActiveSheet.Range("E1:E100") '<<==== CHANGE For Each rCell In rng.Cells With rCell pos = InStr(1, .Value, "(") If pos 0 Then .Value = Left(.Value, pos - 1) End If End With Next rCell End Sub '<<============= Thanks Fellas - GOD BLESS -- holyeart ----------------------------------------------------------------------- holyearth's Profile: http://www.excelforum.com/member.php...fo&userid=3402 View this thread: http://www.excelforum.com/showthread.php?threadid=54910 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Easy Question
Hi HolyEarth,
Change: pos = InStr(1, .Value, "-") to pos = InStr(1, .Value, "(") However, Dave has pointed you to a more efficient solution. --- Regards, Norman "holyearth" wrote in message ... Norman, That worked like a charm!! One last question, Say I have text like this: The world is a nice place (Yes it is) The world is a polluted place (It could be better) How can I modify your macro to delete the contents of the parenthesis and the parenthesis themselves? - To leave only: The world is a nice place The world is a polluted place Thanks Norman, I appreciate your time. -- holyearth ------------------------------------------------------------------------ holyearth's Profile: http://www.excelforum.com/member.php...o&userid=34022 View this thread: http://www.excelforum.com/showthread...hreadid=549103 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Easy Question
That wasn't part of the problem when I posted.
And I screwed up the original suggestion: Record a macro when you select your range edit|replace what: _-* (the underscore represents a space character) with: (leave blank) replace all And continue recording when you do: edit|replace what: _(* (the underscore represents a space character) with: (leave blank) replace all (I dropped the hyphen from the first portion.) holyearth wrote: Hi Dave, This didn't work to remove the ( ) and it's contents... Maybe I did it wrong? -- holyearth ------------------------------------------------------------------------ holyearth's Profile: http://www.excelforum.com/member.php...o&userid=34022 View this thread: http://www.excelforum.com/showthread...hreadid=549103 -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Easy VBA question | Excel Discussion (Misc queries) | |||
| *EASY* question! | New Users to Excel | |||
| new user with easy question? not easy for me | New Users to Excel | |||
| Easy question? | Excel Programming | |||
| Probably the most easy question here... | Excel Programming | |||
Linear Mode
