Remove Hyphens From Multiple Strings

[Buns1976]

Hi Everyone,
I have a single column(I) that currently contains 538 rows of UPC numbers(I1:I538). Each string of UPC numbers contains 3 hyphens(7-49504-3356-2).
I need to remove the hyphens if anyone has a solution for that I can run in a macro?

Thanks,
D

 

[Coyotex3]

Do you need a macro, or would a formula work?

=TEXTJOIN(,,TEXTSPLIT(I1,"-"))

This formula removes the hypen and then joins the text without any delimiters.

 

[rollis13]

Supposing that only your data is in column I you could use this simple macro (or adjust as needed):

Sub test()
    Range("I:I").Replace What:="-", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows
End Sub

 

[Buns1976]

Supposing that only your data is in column I you could use this simple macro (or adjust as needed):

Sub test()
    Range("I:I").Replace What:="-", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows
End Sub

Hey Rollis. Not sure why but I'm not getting any results on the code?

 

[Scott Huish]

Sub test()
Range("I5:I538").Replace "-", ""
End Sub

Formula way:
=SUBSTITUTE(I5,"-","")

 

[rollis13]

Maybe your hyphen isn't so, maybe it's a en dash. Run this test and see what it returns.

Sub test_char()
    MsgBox Asc(Left(Range("I5"), 2))
End Sub

 

[Peter_SSs]

Maybe your hyphen isn't so, maybe it's a en dash. Run this test and see what it returns.

Sub test_char()
    MsgBox Asc(Left(Range("I5"), 2))
End Sub

Doesn't that return the code of the first character in the string? Wouldn't it need to be ..

MsgBox Asc(Mid(Range("I5"), 2))

 

[rollis13]

OMG, Thanks @Peter_SSs, you are right; it was very late last night and I messed up the code .

 

[Buns1976]

Do you need a macro, or would a formula work?

=TEXTJOIN(,,TEXTSPLIT(I1,"-"))

This formula removes the hypen and then joins the text without any delimiters.

If I can find

Doesn't that return the code of the first character in the string? Wouldn't it need to be ..

MsgBox Asc(Mid(Range("I5"), 2))

Sorry folks. I was pulled off on another project so been out of pocket for a few days. The code returned is 45 which reflects a hyphen if I'm not mistaken correct?

 

[rollis13]

Yes, that's it. At this point, please attach a sample of your data.