how to replace space and point by just point character

Posted on 2009-12-30
Last Modified: 2013-11-27

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

Question by:ggc2
    LVL 1

    Author Comment

    Here's an example of a cell that needs the "cleaning"
    LVL 50

    Expert Comment

    by:barry houdini
    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
    LVL 14

    Accepted Solution

    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

    LVL 1

    Author Closing Comment

    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 !

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Article by: Martin
    Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now