MrExcel Publishing
Your One Stop for Excel Tips & Solutions

how can I find and delete . . .


Posted by DANIELLE on June 18, 2001 12:25 PM

I have a list of 300 numbers like this
252-1409 9281
I need to convert it to a barcode so I have to get rid of the dash and the space. Is there a quick way of doing this or do I have to go thru each one


Posted by Mark W. on June 18, 2001 12:37 PM

Use the Edit | Replace... menu command.

Posted by Russell on June 18, 2001 12:38 PM

Will the dash always be in position 4 and the space in position 9?

Posted by danielle on June 18, 2001 12:56 PM

yes

Posted by Russell on June 18, 2001 1:11 PM

Mark's suggestion is good. If you will be needing to repeat the task, you can do it in VBA. If you don't need to repeat the task, just highlight your column, go to Edit - Replace, then type in a "-" (no quotes), and hit "Replace All." Repeat this step, but just type one space into the "find what" box, and hit replace all again. I simply recorded these actions (my data was in column B), and the result is below:

Columns("B:B").Select
Selection.Replace What:="-", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False

Hope this helps,

Russell

Posted by danielle on June 18, 2001 1:19 PM

THANK YOU ! THANK YOU !

IT WORKED AND I OWE YOU GUYS ! IT WAS SO SIMPLE !