+ Reply to Thread
Results 1 to 17 of 17

Preceding Spaces in Excel Formula Removed When Copied

  1. 10-11-2015, 07:06 PM #1
    JYTS
    JYTS is offline
    Forum Contributor
    Join Date
    08-25-2014
    Location
    Pennsylvania, US
    MS-Off Ver
    Windows '16
    Posts
    165

    Preceding Spaces in Excel Formula Removed When Copied

    Hi,

    I have a formula that returns text that is copied and pasted into a PowerPoint presentation on a frequent basis:
    Please Login or Register  to view this content.
    It results in something like this:

    Please Login or Register  to view this content.
    The problem is that when I paste it into PowerPoint, the preceding spaces, for only the first line, are removed. If I add 
    Please Login or Register  to view this content.
    to the beginning of the formula, then the spaces will appear, but so will a period that I don't want. How can I make sure the preceding spaces are copied? Thanks
    Reply With QuoteRegister To Reply
  2. 10-11-2015, 07:35 PM #2
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    42,950

    Re: Preceding Spaces in Excel Formula Removed When Copied

    Maybe try:

    Formula: copy to clipboard 
    Please Login or Register  to view this content.



    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 10-11-2015, 07:55 PM #3
    JYTS
    JYTS is offline
    Forum Contributor
    Join Date
    08-25-2014
    Location
    Pennsylvania, US
    MS-Off Ver
    Windows '16
    Posts
    165

    Re: Preceding Spaces in Excel Formula Removed When Copied

    Thanks for the reply. Unfortunately with that solution, it adds a line break, which I would prefer not to have...
    Reply With QuoteRegister To Reply
  4. 10-11-2015, 08:00 PM #4
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,735

    Re: Preceding Spaces in Excel Formula Removed When Copied

    You already have a line break inthere - thats what CHAR(10) is
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  5. 10-11-2015, 08:02 PM #5
    JYTS
    JYTS is offline
    Forum Contributor
    Join Date
    08-25-2014
    Location
    Pennsylvania, US
    MS-Off Ver
    Windows '16
    Posts
    165

    Re: Preceding Spaces in Excel Formula Removed When Copied

    Yeah, I want those later line breaks in the formula/output, but I don't want it to begin with a line break. I just want those preceding spaces to show up when pasted.
    Last edited by JYTS; 10-11-2015 at 08:08 PM.
    Reply With QuoteRegister To Reply
  6. 10-11-2015, 08:10 PM #6
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    42,950

    Re: Preceding Spaces in Excel Formula Removed When Copied

    CHAR(13) is a Carriage Return, not a Line Break. Did you try it? Alternatively, you could try CHAR(160) which I think is a non breaking space.


    Regards, TMS
    Reply With QuoteRegister To Reply
  7. 10-11-2015, 08:12 PM #7
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,735

    Re: Preceding Spaces in Excel Formula Removed When Copied

    OK, perhaps something like this will keep teh spaces?
    ="a"&REPT(" ",22)&"b"
    Reply With QuoteRegister To Reply
  8. 10-11-2015, 08:18 PM #8
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    42,950

    Re: Preceding Spaces in Excel Formula Removed When Copied

    http://web.itu.edu.tr/~sgunduz/cours...isl/ascii.html
    Reply With QuoteRegister To Reply
  9. 10-11-2015, 08:52 PM #9
    JYTS
    JYTS is offline
    Forum Contributor
    Join Date
    08-25-2014
    Location
    Pennsylvania, US
    MS-Off Ver
    Windows '16
    Posts
    165

    Re: Preceding Spaces in Excel Formula Removed When Copied

    TMS, yes I tried it, and no matter what it is supposed to do, it has the effect, when the cell is copied and pasted into PowerPoint, of being another line break.

    FDibbins, the problem there is that the string would begin with a (non-space) character. I have no problems when I start the formula with a visible character (e.g.: "."&" "...). I need to *start* the strong with a bunch of spaces.
    Reply With QuoteRegister To Reply
  10. 10-11-2015, 10:06 PM #10
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    42,950

    Re: Preceding Spaces in Excel Formula Removed When Copied

    Did you try the non-breaking space?
    Reply With QuoteRegister To Reply
  11. 10-11-2015, 11:31 PM #11
    JYTS
    JYTS is offline
    Forum Contributor
    Join Date
    08-25-2014
    Location
    Pennsylvania, US
    MS-Off Ver
    Windows '16
    Posts
    165

    Re: Preceding Spaces in Excel Formula Removed When Copied

    Yeah, a non-breaking space (CHAR(32)) is essentially what I already have in there, and even if I put CHAR(32)&" ...." in, it still cuts off the preceding spaces when copied into PowerPoint.
    Reply With QuoteRegister To Reply
  12. 10-12-2015, 02:03 AM #12
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    42,950

    Re: Preceding Spaces in Excel Formula Removed When Copied

    Again, suggest you try CHAR(160). CHAR(32) is a normal space.
    Reply With QuoteRegister To Reply
  13. 10-12-2015, 02:56 AM #13
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    42,950

    Re: Preceding Spaces in Excel Formula Removed When Copied

    Ok, just done a small test, populating the cells which are reflected in the cell. I then copied and pasted the cell contents into a PowerPoint slide. It includes the leading spaces.

    Maybe you need to post a sample workbook and corresponding PP presentation. Could be that it's the effect of different versions of Excel. Whatever, it looks fine in Excel/PP 2007 on Windows 7 with no amendment.
    Reply With QuoteRegister To Reply
  14. 10-12-2015, 05:30 AM #14
    thisoldman
    thisoldman is offline
    Registered User
    Join Date
    03-30-2014
    Location
    Pittsburgh,PA
    MS-Off Ver
    Office 2010, Home Student 2013
    Posts
    62

    Re: Preceding Spaces in Excel Formula Removed When Copied

    Would the "No-Width Non Break" followed by the space work? =CHAR(63) or =UNICHAR(8205)
    ⁣ Another possibility ...
    ⁣ ⁣ is the "Invisible Separator",
    ⁣ ⁣ ⁣ =UNICHAR(8291).
    ⁣ ⁣ ⁣ ⁣ It fools the automatic
    ⁣ ⁣ ⁣ ⁣ ⁣ collapsing space mechanism
    ⁣ ⁣ ⁣ ⁣ ⁣ ⁣ in a lot of places,
    ⁣ ⁣ ⁣ ⁣ ⁣ ⁣ ⁣ for instance, allowing the
    ⁣ ⁣ ⁣ ⁣ ⁣ ⁣ ⁣ ⁣ increasing indentation of
    ⁣ ⁣ ⁣ ⁣ ⁣ ⁣ ⁣ ⁣ ⁣ these lines.
    Last edited by thisoldman; 10-12-2015 at 05:36 AM.
    Reply With QuoteRegister To Reply
  15. 10-12-2015, 10:59 AM #15
    JYTS
    JYTS is offline
    Forum Contributor
    Join Date
    08-25-2014
    Location
    Pennsylvania, US
    MS-Off Ver
    Windows '16
    Posts
    165

    Re: Preceding Spaces in Excel Formula Removed When Copied

    TMS, CHAR(160) worked perfectly. Thanks!

    Also, thanks for your reply thisoldman.
    Reply With QuoteRegister To Reply
  16. 10-12-2015, 11:41 AM #16
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    42,950

    Re: Preceding Spaces in Excel Formula Removed When Copied

    You're welcome. Thanks for the rep.



    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.
    Reply With QuoteRegister To Reply
  17. 10-12-2015, 01:24 PM #17
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    42,950

    Re: Preceding Spaces in Excel Formula Removed When Copied

    Forgot to say ... how many times did I suggest CHAR(160)
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Delete preceding spaces
    By kjxavier in forum Excel General
    Replies: 2
    Last Post: 09-23-2014, 12:03 AM
  2. Remove spaces in data copied from Excel
    By antonf in forum Word Formatting & General
    Replies: 14
    Last Post: 02-23-2014, 10:24 PM
  3. [SOLVED] Formula to insert text in new column if preceding more than one of the preceding have val
    By aimeecrystalaid in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-04-2013, 11:47 PM
  4. Deleting spaces preceding text in copied in data
    By cloudwalking in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-04-2013, 09:08 AM
  5. putting spaces between copied columns in excel sheets
    By rjhe22 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-04-2013, 06:39 AM
  6. How do you delete spaces that get copied into excel?
    By Excelatexcel in forum Excel General
    Replies: 3
    Last Post: 09-18-2011, 11:49 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1