Easy Question

[holyearth]

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]

^Bump^ ^Bump^

[holyearth]

^Bumpetty Bump^

[Norman Jones]

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Norman Jones]

Hi HolyEarth,

> ^Bump^ ^Bump^


A bump after six minutes!!!


As a matter of interest, how quickly had you anticipated receiving a
response?


---
Regards,
Norman

[holyearth]

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.

[Dave Peterson]

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

[holyearth]

Hi Dave,

This didn't work to remove the ( ) and it's contents...


Maybe I did it wrong?

[holyearth]

Hey Norman,

Sorry but i'm over anxious and too high-strung :-)

[holyearth]

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.

[Norman Jones]

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Dave Peterson]

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