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

Marq

Well-known Member
Joined
Dec 13, 2004
Messages
854
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?
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,879
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 13, 2004
Messages
854
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

Well-known Member
Joined
Apr 11, 2012
Messages
9,745
If it's a regular blank space, this macro should do what you want.
VBA Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,879
Office Version
  1. 365
Platform
  1. Windows
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").
 

Watch MrExcel Video

Forum statistics

Threads
1,114,255
Messages
5,546,794
Members
410,758
Latest member
Papers
Top