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

 

[Buns1976]

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

This is how I receive it. The leading zero has to remain but the hyphens must be removed. Thank You

BRAND	ITEM#	ITEM DESCRIPTION	COMMENT	BEGIN	END	EACH SIZE	RETAIL UPC	PPTR	PPTR	CHG	PPTR
B L	53030	BL 30 CAN		1/29/2024	3/3/2024	12 OZ	0-18200-53030-2	$ 19.75	$ 19.75	$ -	$ 19.75

 

[Peter_SSs]

The code returned is 45 which reflects a hyphen if I'm not mistaken correct?

Thanks for the confirmation.

This is how I receive it.

Slight issue is that we cannot see from that table what the columns are. If they start at column A as would be most common, then the column in question would be column H, not column I as stated in post 1.

Assuming that it is column I then try this code with a copy of your workbook.

Sub RemoveHyphens()
  With Range("I2", Range("I" & Rows.Count).End(xlUp)) '<- Adjust column if required.
    .NumberFormat = "@"
    .Value = Evaluate("substitute(" & .Address & ",""-"","""")")
  End With
End Sub

Before:

Buns1976.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		BRAND	ITEM#	ITEM DESCRIPTION	COMMENT	BEGIN	END	EACH SIZE	RETAIL UPC	PPTR	PPTR	CHG	PPTR
2		B L	53030	BL 30 CAN		1/29/2024	3/03/2024	12 OZ	0-18200-53030-2	$19.75	$19.75	$ -	$19.75
Sheet3

After:

Buns1976.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		BRAND	ITEM#	ITEM DESCRIPTION	COMMENT	BEGIN	END	EACH SIZE	RETAIL UPC	PPTR	PPTR	CHG	PPTR
2		B L	53030	BL 30 CAN		1/29/2024	3/03/2024	12 OZ	018200530302	$19.75	$19.75	$ -	$19.75
Sheet3

 

[Buns1976]

Thanks for the confirmation.



Slight issue is that we cannot see from that table what the columns are. If they start at column A as would be most common, then the column in question would be column H, not column I as stated in post 1.

Assuming that it is column I then try this code with a copy of your workbook.

Sub RemoveHyphens()
  With Range("I2", Range("I" & Rows.Count).End(xlUp)) '<- Adjust column if required.
    .NumberFormat = "@"
    .Value = Evaluate("substitute(" & .Address & ",""-"","""")")
  End With
End Sub

Before:

Buns1976.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		BRAND	ITEM#	ITEM DESCRIPTION	COMMENT	BEGIN	END	EACH SIZE	RETAIL UPC	PPTR	PPTR	CHG	PPTR
2		B L	53030	BL 30 CAN		1/29/2024	3/03/2024	12 OZ	0-18200-53030-2	$19.75	$19.75	$ -	$19.75
Sheet3

After:

Buns1976.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		BRAND	ITEM#	ITEM DESCRIPTION	COMMENT	BEGIN	END	EACH SIZE	RETAIL UPC	PPTR	PPTR	CHG	PPTR
2		B L	53030	BL 30 CAN		1/29/2024	3/03/2024	12 OZ	018200530302	$19.75	$19.75	$ -	$19.75
Sheet3

Yes Sir, That does the trick. The data does start in column b. Reason unknow to me but column a is hidden by the vendor who supplies the information.

Thank You kindly!
B

 

[Peter_SSs]

You're welcome. Thanks for the feedback.