removing spaces in barcode

**Navop** · 01-09-2012, 11:17 PM

I have a table with approximately 1000+ lines of bar-codes looking like this the info is all in one column which is C

7 84695 99241 3
7 84695 99242 0
784695 99243 7
7 84695 992444
7 84695992451
7 84695 99246 8

the space is all over not sure why the bar-codes aren't entered all the same way,
what I need is that all the space must be removed

I have tried =substitute(c1," ","") which works fine but now I must copy this formula for all of the column C

Is their a range function or vb code to do this for all the column C

thanks

**NickyC** · 01-10-2012, 02:39 AM

You could do this in several ways, here are three that might work

1. select column C and run search and replace entering a space in the search box

2. Add a column to the right of column C, enter your fomula in cell D1, copy it into all the cells in column D adjacent to a value in column C. Select the contents of column D and click copy > paste special > paste values

3. Run this macro:

Sub substitute_blanks()
Dim n
For n = 1 To ActiveSheet.UsedRange.Rows.Count + 1
    Columns("C").Cells(n).Value = Replace(Columns("C").Cells(n).Value, " ", "")
Next n
End Sub

**zbor** · 01-10-2012, 02:57 AM

Or this code for selected cells:
(select cells and run macro)

Public Sub Remove_space()
Dim rngArea As Range
For Each rngArea In Selection.Areas
    With rngArea
        .Value = Evaluate("IF(ISTEXT(" & .Address & "),SUBSTITUTE(" & .Address & ","" "",""""),REPT(" & .Address & ",1))")
        .NumberFormat = "0"
    End With
Next rngArea
End Sub

Thread: removing spaces in barcode

Thread Tools

Display

removing spaces in barcode

Re: removing spaces in barcode

Re: removing spaces in barcode

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions