Quick string thing!!!!

[Guest]

Hi,

In my excel sheet I have data such as :

MAC-103-234-234
MAC-103-234-234
MAC-103-234-234
MAC-103-234-234

I need to delete all the "-" and leave:

MAC103234234
MAC103234234
MAC103234234
MAC103234234

Any suggestions??

Cheers

Manny

 

[Tom Ogilvy]

select the column and do

Edit=>Replace
What -
With leave blank

 

[Guest]

Needs to be done in a macro.

But thanks anyway

select the column and do

Edit=>Replace
What -
With leave blank

 

[kkknie]

The code would be:

Sub Test()

For Each r In Range("A1:A4")

r.Value = Replace(r.Value, "-", "")

Next

End Sub

Substitute your a range for A1:A4.

 

[Tom Ogilvy]

Just turn on the macro recorder while you do it manually.

It is a single command.

What could be simpler.

Here, I will do it for you:

Sub ReplaceHyphen()
Columns(1).Replace What:="-", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

End Sub

--
Regards,
Tom Ogilvy

 

[Tom Ogilvy]

Just a thought since you said nothing but "the code would be", then
presented a different approach than what was suggest by me.

Using the Replace Method without looping would be much faster than using the
replace function in a loop. For 4 cells, it wouldn't be noticeable, but for
1000 (for example) it would.

 

[Fred]

Hi,

In my excel sheet I have data such as :

MAC-103-234-234
MAC-103-234-234
MAC-103-234-234
MAC-103-234-234

I need to delete all the "-" and leave:

MAC103234234
MAC103234234
MAC103234234
MAC103234234

Any suggestions??

Cheers

Manny

This will select the entire current region and remove all -. If you
want to do it by a column use Columns("A:A").Select instead of
Selection.CurrentRegion.Select.

Fred (e-mail address removed)

------
Sub remove_hyphen()
Selection.CurrentRegion.Select
Selection.Replace What:="-", Replacement:="", LookAt:=xlPart,
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False
End Sub
-------

 

[Fred]

Hi,

In my excel sheet I have data such as :

MAC-103-234-234
MAC-103-234-234
MAC-103-234-234
MAC-103-234-234

I need to delete all the "-" and leave:

MAC103234234
MAC103234234
MAC103234234
MAC103234234

Any suggestions??

Cheers

Manny

Actually all you need is this:

-----
Sub remove_hyphen()
Selection.CurrentRegion.Select
Selection.Replace What:="-", Replacement:=""
End Sub
------

TIP: What I did was record a Macro doing it manually like Tom
reccomended. Then just cleaned up the code.

Fred

 

[kkknie]

Tom,

You are 100% correct. When I wrote "The code would be", I should hav
said "a code that will work but may not be the best possible would be"
Much appologies.

K

P.S. Thank you for the denigration