how to replace space and point by just point character


I have an excel file with thousands of cells containing product descriptions.

some cells have spaces in them that need to be cleaned out.  the space is always found for the point charactar (046) AND/OR on the next line of the description for instance :

Afmetingen: 13,5x33 cm .
 Pagina-indeling: 1 dag over 2 pagina's.

this cell has a space before the point. i would like the point to be against the text.  

i tried excel function replace " ." by "." but that doesn't work.

Also on the beginning of the next line, just before "Pagina..." there's a space character. this space character has to go as well.
extra note : there's ALWAYS an line feed behind the point of the previous line.  so the find replace could be:

replace "point+line feed+space" by "point+line feed" but that also doesn't work.

i would like a macro or something which allows me to select the cells and that replaces these obsolete characters.

Does anyone know how to do this

Who is Participating?
Tommy KinardConnect With a Mentor Commented:
This should work.


Sub CleanDots()
    Dim Rng As Range, AllRng As Range
    Dim mData() As String, mI As Long
    Set AllRng = Selection
    For Each Rng In AllRng
        mData = Split(Rng.Text, vbLf)
        For mI = LBound(mData, 1) To UBound(mData, 1)
            mData(mI) = Trim(mData(mI))
            mData(mI) = Replace(mData(mI), " .", ".")
        Cells(Rng.Row, Rng.Column) = Join(mData, vbLf)
End Sub

Open in new window

ggc2Author Commented:
Here's an example of a cell that needs the "cleaning"
barry houdiniCommented:
You should be able to use Edit > Replace.
It worked for me like this:
Select range
Edit > Replace and in the "replace what" box type a space, a point and then, using the Numberpad, type 0010 while holding down the ALT key.
In replace with just use a point followed by a space.
I got 4 replacements using that method.
Note the Line Break is CHAR(10) hence the 0010
regards, barry
ggc2Author Commented:
Thank you dragontooth, your solution worked for me !

Sorry for the late response, i had some other obligations during this period.

Happy new year to you as well !
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.