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

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

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

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

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

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

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

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

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

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

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

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