+ Reply to Thread
Results 1 to 9 of 9

How do I remove a blanck space from the beginning of a formula?

  1. #1
    kan2953
    Guest

    How do I remove a blanck space from the beginning of a formula?

    I have a leading space in my formula, which is causing it not to function.
    How do I get rid of it?

  2. #2
    Registered User
    Join Date
    04-05-2005
    Posts
    15

    How do I remove a blanck space from the beginning of a formula? Reply to Thread

    You can use the Trim function but I have found it quicker to use VBA

    Dim r As Object

    For Each r In Range("rCheck")
    r.Value = Trim(r.Value)
    Next

    Range rCheck being the range where the formulae need changing

    Hope this helps

    Badger

  3. #3
    JulieD
    Guest

    Re: How do I remove a blanck space from the beginning of a formula?

    Hi

    select the formulas and choose edit / replace
    in the find what box press your space bar
    leave the replace with box empty

    press Replace All

    NOTE: when trying something new like this it is always a good idea to do it
    on a copy of your workbook first ... just to ensure that the results you get
    are the results you expect.

    --
    Cheers
    JulieD
    check out www.hcts.net.au/tipsandtricks.htm
    ....well i'm working on it anyway
    "kan2953" <[email protected]> wrote in message
    news:[email protected]...
    >I have a leading space in my formula, which is causing it not to function.
    > How do I get rid of it?




  4. #4
    Ken Hudson
    Guest

    RE: How do I remove a blanck space from the beginning of a formula?

    Hi,
    Not sure what you mean by a space at the beginning of a formula.
    To remove a leading space from the contents of a cell, use the TRIM function.
    If A1 contains (space)ABC, then, if you enter =TRIM(A1) in cell B2, the
    contents of B2 will be ABC.

    "kan2953" wrote:

    > I have a leading space in my formula, which is causing it not to function.
    > How do I get rid of it?


  5. #5
    David McRitchie
    Guest

    Re: How do I remove a blanck space from the beginning of a formula?

    If you have a space before the equal sign in the formula
    that you want to eliminate so the formula can function
    you can use the TrimALL macro found on my page
    http://www.mvps.org/dmcritchie/excel/join.htm#trimall
    ---
    HTH,
    David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
    My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
    Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

    "kan2953" <[email protected]> wrote in message news:[email protected]...
    > I have a leading space in my formula, which is causing it not to function.
    > How do I get rid of it?




  6. #6
    David McRitchie
    Guest

    Re: How do I remove a blanck space from the beginning of a formula?

    Would be okay if the only spaces are unwanted
    spaces before the the equal sign, but it would be
    better to replace space equal sign by equal sign.
    so as not to destroy embedded spaces as in
    i.e. = A1 & " " & B1

    The TrimAll macro on my join.htm page will convert
    CHAR(128) to spaces first and then TRIM. Used all the
    time as a fixup for bad typing introducing spaces and for
    copying tables from web pages where the spaces are
    really &nbsp; (nonbreaking spaces).
    ---
    HTH,
    David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
    My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
    Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

    "JulieD" <[email protected]> wrote in message news:[email protected]...
    > Hi
    >
    > select the formulas and choose edit / replace
    > in the find what box press your space bar
    > leave the replace with box empty
    >
    > press Replace All
    >
    > NOTE: when trying something new like this it is always a good idea to do it
    > on a copy of your workbook first ... just to ensure that the results you get
    > are the results you expect.
    >
    > --
    > Cheers
    > JulieD
    > check out www.hcts.net.au/tipsandtricks.htm
    > ...well i'm working on it anyway
    > "kan2953" <[email protected]> wrote in message
    > news:[email protected]...
    > >I have a leading space in my formula, which is causing it not to function.
    > > How do I get rid of it?

    >
    >




  7. #7
    David McRitchie
    Guest

    Re: How do I remove a blanck space from the beginning of a formula?

    Hi Ken,
    If you use the TRIM Worksheet Function, you will remove the
    leading space, but it will be your TRIM formula that is in use
    and it will be text. Converting the formula to values would
    have a text value that looks like a formula, you would then have
    to hit F2 and Enter for each cell or run a macro that does reenters

    Better solution, I think, is to use the TRIMALL macro in
    http://www.mvps.org/dmcritchie/excel/join.htm#trimall
    and change the cells right where they are.
    ---
    HTH,
    David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
    My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
    Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

    "Ken Hudson" <[email protected]> wrote in message news:[email protected]...
    > Hi,
    > Not sure what you mean by a space at the beginning of a formula.
    > To remove a leading space from the contents of a cell, use the TRIM function.
    > If A1 contains (space)ABC, then, if you enter =TRIM(A1) in cell B2, the
    > contents of B2 will be ABC.
    >
    > "kan2953" wrote:
    >
    > > I have a leading space in my formula, which is causing it not to function.
    > > How do I get rid of it?




  8. #8
    JulieD
    Guest

    Re: How do I remove a blanck space from the beginning of a formula?

    Hi David

    you're absolutely correct ... don't know where my brain was when i typed
    that answer

    --
    Cheers
    JulieD
    check out www.hcts.net.au/tipsandtricks.htm
    ....well i'm working on it anyway
    "David McRitchie" <[email protected]> wrote in message
    news:%[email protected]...
    > Would be okay if the only spaces are unwanted
    > spaces before the the equal sign, but it would be
    > better to replace space equal sign by equal sign.
    > so as not to destroy embedded spaces as in
    > i.e. = A1 & " " & B1
    >
    > The TrimAll macro on my join.htm page will convert
    > CHAR(128) to spaces first and then TRIM. Used all the
    > time as a fixup for bad typing introducing spaces and for
    > copying tables from web pages where the spaces are
    > really &nbsp; (nonbreaking spaces).
    > ---
    > HTH,
    > David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
    > My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
    > Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
    >
    > "JulieD" <[email protected]> wrote in message
    > news:[email protected]...
    >> Hi
    >>
    >> select the formulas and choose edit / replace
    >> in the find what box press your space bar
    >> leave the replace with box empty
    >>
    >> press Replace All
    >>
    >> NOTE: when trying something new like this it is always a good idea to do
    >> it
    >> on a copy of your workbook first ... just to ensure that the results you
    >> get
    >> are the results you expect.
    >>
    >> --
    >> Cheers
    >> JulieD
    >> check out www.hcts.net.au/tipsandtricks.htm
    >> ...well i'm working on it anyway
    >> "kan2953" <[email protected]> wrote in message
    >> news:[email protected]...
    >> >I have a leading space in my formula, which is causing it not to
    >> >function.
    >> > How do I get rid of it?

    >>
    >>

    >
    >




  9. #9
    Registered User
    Join Date
    04-06-2005
    Posts
    3

    ?

    why couldnt you just hit the F2 and move the curser in front of the space and hit the delete key?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1