+ Reply to Thread
Results 1 to 12 of 12

Autofill will not work with quotes around text

  1. #1
    Registered User
    Join Date
    12-04-2019
    Location
    USA
    MS-Off Ver
    Pro Plus 2013
    Posts
    6

    Autofill will not work with quotes around text

    Hello. This is my first post on this forum and I hope one of you can help me with my autofill issue.

    I am creating a spreadsheet which will contain new user info that I will import via Powershell. So in a cell I typed -Name "user1". (I should note here also that I had to format the entire sheet to text because Excel kept seeing -Name as a formula because of the - symbol.) When I drag the handle down it just copies the same text without changing the ending number sequentially. (-Name "user2", -Name "user3"etc.) I noticed that when I remove the quotes from around "user1" then it will autofill down correctly. So obviously the quotes are my roadblock but they must be there when I import into Powershell or the commands will fail. Any thoughts? Thank you.

  2. #2
    Forum Expert Sambo kid's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office 365 ProPlus - work; 2012 for Mac at home
    Posts
    5,920

    Re: Autofill will not work with quotes around text

    try it this way...
    '-Name user 1
    then drag down, works in my workbook and indexes to user 2 and so on.

    Of course that is assuming you don't want the quotes around user and you do not keep the workbook formatted as text, no formulas will work in an all text workbook.
    Last edited by Sambo kid; Yesterday at 06:12 PM.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Registered User
    Join Date
    12-04-2019
    Location
    USA
    MS-Off Ver
    Pro Plus 2013
    Posts
    6

    Re: Autofill will not work with quotes around text

    Yes, as I stated the quotes MUST be around user1 otherwise my Powershell commands will fail.

  4. #4
    Forum Expert Sambo kid's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office 365 ProPlus - work; 2012 for Mac at home
    Posts
    5,920

    Re: Autofill will not work with quotes around text

    How about the issue with the cells being formatted as text, is that necessary or can they be general? If they can be general then a formula can be written.

  5. #5
    Registered User
    Join Date
    12-04-2019
    Location
    USA
    MS-Off Ver
    Pro Plus 2013
    Posts
    6

    Re: Autofill will not work with quotes around text

    Well...you will notice that when you type -Name "User1" it treats it as a formula because of the "-" symbol. If you know how to get around that then you are my hero.

  6. #6
    Forum Expert Sambo kid's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office 365 ProPlus - work; 2012 for Mac at home
    Posts
    5,920

    Re: Autofill will not work with quotes around text

    I tried in one step but it failed, I can get it there in three steps and will have to consider it again for one step. But I am uploading the file in case you can use the process.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    12-04-2019
    Location
    USA
    MS-Off Ver
    Pro Plus 2013
    Posts
    6

    Re: Autofill will not work with quotes around text

    I'll take it!

    When I have 50+ accounts to create at once this will be a BIG help. Thank you. If you do find a 1 step solution please let me know. Thanks again.

  8. #8
    Forum Expert Sambo kid's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office 365 ProPlus - work; 2012 for Mac at home
    Posts
    5,920

    Re: Autofill will not work with quotes around text

    this is down to two steps...
    step one put Name User1 in one cell (say A1) then this in B1... ="-"&SUBSTITUTE(A1," "," ''")&MID(A1,99,99)&"''"
    Will keep thinking on it for one step.

  9. #9
    Forum Expert Sambo kid's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office 365 ProPlus - work; 2012 for Mac at home
    Posts
    5,920

    Re: Autofill will not work with quotes around text

    Ok, got it in one formula...
    ="-Name"&" "&"''User"&ROW(A1)&"''"
    put wherever you want and drag down.

  10. #10
    Registered User
    Join Date
    12-04-2019
    Location
    USA
    MS-Off Ver
    Pro Plus 2013
    Posts
    6

    Re: Autofill will not work with quotes around text

    Beautiful solution. Thank you. If I could give you extra Reputation I would.

  11. #11
    Forum Expert Sambo kid's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office 365 ProPlus - work; 2012 for Mac at home
    Posts
    5,920

    Re: Autofill will not work with quotes around text

    Thank you for the rep! If possible please mark as solved using the thread tools dropdown at the top.
    And BTW, not to beat a dead horse but found a way to make it a bit shorter with same output in case you are interested.

    ="-Name"&" ''User"&ROW(A1)&"''"

    realized I didn't need one of the ampersands and quotes so removed that.

  12. #12
    Registered User
    Join Date
    12-04-2019
    Location
    USA
    MS-Off Ver
    Pro Plus 2013
    Posts
    6

    Re: Autofill will not work with quotes around text

    Thank you!

+ 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