+ Reply to Thread
Results 1 to 28 of 28

Spaces!!!

  1. #1
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Spaces!!!

    Three parter;

    1) Why do people entering data put spaces at the end of text they are typing?

    2) Why does this cause me anxiety when I watch someone do it?

    3) Or is it just me?

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,411

    Re: Spaces!!!

    What exactly do you mean?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Spaces!!!

    This - "text "
    As opposed to this - "text"

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,411

    Re: Spaces!!!

    Where do you see this hapening?

  5. #5
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Spaces!!!

    All the time in my workplace. Workbooks maintained by many different people. My colleague who sits next me.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,411

    Re: Spaces!!!

    Oh, dear. Have you thought of asking them why they do it?

  7. #7
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Spaces!!!

    It just seems so obvious to me that you wouldn't put a space. Why expend valuable energy that could be put to better use, like mining bitcoins?

    Maybe I have the problem?

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,411

    Re: Spaces!!!

    The problem is that it has become a learned procedure for them - they won't even know they are doing it, or why. Their muscles will have learned to do it and it may be hard for them to unlearn. Why they have been in the habit of doing it in the first place, only they can answer, but unless you point it out and tell them why it could cause problems, they won't be able to put it right.

    I can't say I've noticed much of it myself.

  9. #9
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Spaces!!!

    Please Login or Register  to view this content.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Spaces!!!

    Oh I have seen it... The HR departmentof my previous employer was staffed by numpties who randonly inserted variable numbers of spaces everywhere, who merged cells randomly... even in the middle of tables... and treated excel like a stone slab and a chisel.

    They could never understand when I threw their sheets back at them, requesting the provision of remedial "Excel for numpties" classes for them all. This was then passed on to IT.... and suddenly it got worse.... the blind leading the blind.

    Rage, fume, rant.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  11. #11
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,887

    Re: Spaces!!!

    Quote Originally Posted by Glenn Kennedy View Post
    who merged cells randomly... even in the middle of tables
    I assume you don't mean that in the Excel sense of the word 'tables'?
    Rory

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Spaces!!!

    No.... "blocks of data" would have been more accurate: but this sort of thing:

    Untitled.png

  13. #13
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,887

    Re: Spaces!!!

    Yeah, that makes my blood boil. I try and persuade my colleagues to use tables when practical. For all their drawbacks, at least you can't merge cells in them!

  14. #14
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Spaces!!!

    What drawbacks?

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,411

    Re: Spaces!!!

    My pet hate is receiving data via a .csv file that suddenly has a random space at the beginning of the cells in each column, and not one that TRIM will deal with. I see that a lot - I don't think it's user error, rather teh way that the data has been parsed to the .csv file. Cleansing this sort of data is a pain!

  16. #16
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,887

    Re: Spaces!!!

    Quote Originally Posted by Olly View Post
    What drawbacks?
    They're slow; don't work on protected sheets; conditional formatting gets messed up easily (though that's not just Tables); have numerous VBA issues; structured referencing still has holes; can't use formulas in headings...

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,411

    Re: Spaces!!!

    Why don't they work on protected sheets? Have you set the protection correctly? They work on mine!

  18. #18
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,887

    Re: Spaces!!!

    You can insert a table row on a protected sheet?

  19. #19
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,411

    Re: Spaces!!!

    Yes - as long as Insert Rows is checked in the protection rules dialog. It isn't by default.

  20. #20
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Spaces!!!

    What VBA issues do you have with listobjects?

  21. #21
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,887

    Re: Spaces!!!

    Quote Originally Posted by AliGW View Post
    Yes - as long as Insert Rows is checked in the protection rules dialog. It isn't by default.
    A Table row specifically, not an entire new row? If so, I guess they finally fixed it.

  22. #22
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,887

    Re: Spaces!!!

    Quote Originally Posted by Olly View Post
    What VBA issues do you have with listobjects?
    There are several things, like not being able to delete a table row if it's filtered. There's no error though, the code just does nothing.

  23. #23
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,411

    Re: Spaces!!!

    Quote Originally Posted by rorya View Post
    A Table row specifically, not an entire new row? If so, I guess they finally fixed it.
    A whole row, yes, not a table row specifically. I've never found this a limitation, though.

  24. #24
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012
    Quote Originally Posted by Olly View Post
    Please Login or Register  to view this content.
    Thanks Olly!

  25. #25
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012
    Quote Originally Posted by Glenn Kennedy View Post
    Oh I have seen it... The HR departmentof my previous employer was staffed by numpties
    Hahaha numpties hiring more numpties.

  26. #26
    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,917

    Re: Spaces!!!

    Quote Originally Posted by AliGW View Post
    My pet hate is receiving data via a .csv file that suddenly has a random space at the beginning of the cells in each column, and not one that TRIM will deal with. I see that a lot - I don't think it's user error, rather teh way that the data has been parsed to the .csv file. Cleansing this sort of data is a pain!
    Ali, very true about muscle memory. I have that problem when typing "the", it often ends up as "teh" (like some one else, I see ) I try to stop doing it, but it kinda slips my mind when Im typing. (Because I do it so often, my eye has become accustomed to look for it LOL)
    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

  27. #27
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Spaces!!!

    Yes, data entry consistency is a bit of issue. I did hold training sessions several times. But old habits die hard, and they always reverted to their old ways after a time.

    Now, I don't bother with training and set up data entry forms with strict pattern matching.

    Ex: For HR file identifier, they used to use loose convention. Employee name Some notes yyyy.

    Now they are forced to conform to RegEx pattern set, dropdown, checkbox, radial etc on all but the notes field (using ASP .Net & other tools for data entry, as I found Excel bit too flexible and easy to break).
    There was a lot of complaints at first.
    But once I convinced CFO of benefits... everyone got used to it fast.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  28. #28
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,411

    Re: Spaces!!!

    Ford - thanks for teh reminder ...

    I am always typing woudl instead of would!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Formula To Create a List Without Spaces From Data With Spaces
    By nicklasnicklas in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-11-2017, 01:58 AM
  2. How to reference spaces as spaces not zeros
    By Rob K in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-23-2015, 04:56 AM
  3. [SOLVED] Replacing double spaces with single spaces
    By EdWoods in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 04-15-2013, 01:20 AM
  4. [SOLVED] sheet1 row with spaces between cells, pulled to sheet2 column with no spaces between cells
    By chestersneakers7 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-18-2013, 05:56 PM
  5. spaces not recognized as spaces
    By windsurferLA in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-27-2006, 06:49 AM
  6. spaces not recognized as spaces
    By windsurferLA in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 07-26-2006, 03:05 PM
  7. spaces not recognized as spaces
    By windsurferLA in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-26-2006, 12:45 PM

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