+ Reply to Thread
Results 1 to 13 of 13

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
    7

    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 Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    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 Sam Capricci; 12-04-2019 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
    7

    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 Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    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
    7

    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 Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    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
    7

    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 Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    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 Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    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
    7

    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 Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    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
    7

    Re: Autofill will not work with quotes around text

    Thank you!

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

    Re: Autofill will not work with quotes around text

    Quote Originally Posted by Sambo kid View Post
    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.
    When I paste the formula in a cell it changes User01 to User11 and then after 19 it jumps to 110.

    ***Edit: I had to start with User0 for it to correctly drag down from 1 to 30.
    Last edited by jtlancer; 12-11-2019 at 10:56 PM.

+ 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. Using a variable in a Formula: Quotes don't work
    By WmBarth in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-15-2015, 05:06 AM
  2. Remove quotes in text file after converting Excel to Text in VBA Access 2002
    By ucalcen in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-13-2014, 10:35 AM
  3. [SOLVED] Double Quotes using Chr(34) and Extra Quotes when saving as text file
    By vamosj in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-26-2013, 12:26 PM
  4. autofill with VBA formula that contains quotes
    By scruise in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-11-2012, 10:17 AM
  5. Remove text between quotes, variable length and number of quotes
    By JP Romano in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-14-2011, 10:40 AM
  6. Replies: 6
    Last Post: 07-11-2006, 02:10 PM
  7. Writing text string with imbedded quotes to a text stream
    By John Wirt in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-08-2005, 09:05 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