How do I remove a blank space after a string of numbers?

[Marq]

I have a work book with over 3,000 rows of data. One column (AL) has a work order number in it that has thirteen characters which looks like this 12345678-9100

That 14th position is blank and it is screwing up a sumproduct formula i am using.

How do I remove that 14th blank spot...basically i want to back space it to remove it.

I tried control/H and hit 14 space bars in the "find what"...and didnt space bar anything in "replace with". I didnt expect to work and of course it did not.

every entry is unique so I cant correct one and copy and paste.... and it will take forever for me to go into each cell and back space that 14th spot.

So how would i fix this?

 

[Joe4]

If the data came from the Web or another application, it may be a special character, which is why replacing the space is not working.
If ll your entries are the same length, you can simply to a Text to Columns on that column, selecting the Fixed Width option, putting the split after the 13th character. You can then elect to discard/not import anything after that 13th space.

 

[Marq]

If the data came from the Web or another application, it may be a special character, which is why replacing the space is not working.
If ll your entries are the same length, you can simply to a Text to Columns on that column, selecting the Fixed Width option, putting the split after the 13th character. You can then elect to discard/not import anything after that 13th space.

entries are not from the web.

I cant do the Text to Columns because some entries are 30 characters long and others are 13 (14 including the blank space). The 30 characters long entries are ok so nothing has to be corrected with those and the text to columns would effect those 30 string items...those 30 stringers NEED that blank space int he 14th spot.

 

[mumps]

If it's a regular blank space, this macro should do what you want.

Sub TrimVals()
    Application.ScreenUpdating = False
    Dim rng As Range
    For Each rng In Range("AL1", Range("AL" & Rows.Count).End(xlUp))
        rng = Trim(rng)
    Next rng
    Application.ScreenUpdating = False
End Sub

 

[Joe4]

I cant do the Text to Columns because some entries are 30 characters long and others are 13 (14 including the blank space). The 30 characters long entries are ok so nothing has to be corrected with those and the text to columns would effect those 30 string items...those 30 stringers NEED that blank space int he 14th spot.

OK, let's find out exactly what that last character is.
Pick any entry with this "space". Let's say it is cell J10.
Then enter this formula in any blank cell on your sheet and tell us what it returns:
=CODE(RIGHT(J10,1))

This will tell us the ASCII character of that last character. So then we can look up what it is (regular spaces are "32").