• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

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

  • 2
1 Solution
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
Tommy KinardCommented:
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:
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 !

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now