+ Reply to Thread
Results 1 to 10 of 10

Adding a character after each string in cell

  1. #1
    Valued Forum Contributor
    Join Date
    03-03-2009
    Location
    UK
    MS-Off Ver
    MS365 Subscription Excel for Mac
    Posts
    1,017

    Adding a character after each string in cell

    In cells E2:E4 I have a string of text

    I want a function that adds the character % after each string in each cell.

    So in cell E2 I have the word Cat
    In cell E3 Dog
    In cell E4 Rat

    I want the output in cell F1 to be Cat%Dog%Rat
    I can do this fine with the below code however if there is a blank cell it will output as %Dog%Rat for example (if E2 is blank) rather than what I want which would be Dog%Rat

    Please Login or Register  to view this content.
    How do I adjust it so that if a cell is blank it ignores it rather than still adding the % symbol?

  2. #2
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Türkiye
    MS-Off Ver
    2010 - 64 Bit on Win7
    Posts
    974

    Re: Adding a character after each string in cell

    The function seems OK to me ....
    Attached Images Attached Images

  3. #3
    Valued Forum Contributor
    Join Date
    03-03-2009
    Location
    UK
    MS-Off Ver
    MS365 Subscription Excel for Mac
    Posts
    1,017

    Re: Adding a character after each string in cell

    @Halluk - Clear cell E3 and then it will output %rat%cat which is incorrect. It should output rat%cat

  4. #4
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Türkiye
    MS-Off Ver
    2010 - 64 Bit on Win7
    Posts
    974

    Re: Adding a character after each string in cell

    I've cleared cell E3 and still it's OK....
    Attached Images Attached Images

  5. #5
    Valued Forum Contributor
    Join Date
    03-03-2009
    Location
    UK
    MS-Off Ver
    MS365 Subscription Excel for Mac
    Posts
    1,017

    Re: Adding a character after each string in cell

    Weird. Not for me. Is there a way then to automatically remove % if it's the first character in the transposed range? And if 2 % symbols are in the transposed range directly next to each other to remove 1 of them?

  6. #6
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Türkiye
    MS-Off Ver
    2010 - 64 Bit on Win7
    Posts
    974

    Re: Adding a character after each string in cell

    If the cells in range E2:E5 is populated with a function which gets the data from other cells or via a condition, then; the cell you see as blank is not actually empty but it contains a null string.

    So, your function treats these kind of cells as non-empty and adds a "%" character.
    Last edited by Haluk; 07-14-2020 at 08:07 AM. Reason: typo...

  7. #7
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Türkiye
    MS-Off Ver
    2010 - 64 Bit on Win7
    Posts
    974

    Re: Adding a character after each string in cell

    Having said this, your function may be revised as;

    Please Login or Register  to view this content.

  8. #8
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    11,672

    Re: Adding a character after each string in cell

    Works for me as well...
    Try...
    Please Login or Register  to view this content.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  9. #9
    Valued Forum Contributor
    Join Date
    03-03-2009
    Location
    UK
    MS-Off Ver
    MS365 Subscription Excel for Mac
    Posts
    1,017

    Re: Adding a character after each string in cell

    Excellent. Works now! Thanks all

  10. #10
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010, 2019
    Posts
    2,268

    Re: Adding a character after each string in cell

    I see this thread has been marked SOLVED, but I thought readers of this thread might find it interesting to know that if the range is always vertical and contiguous, there is a one-liner function available to do what the OP asked for...
    Please Login or Register  to view this content.
    EDIT NOTE: I should mention that the above works only if the value in each cell have no spaces within them. If there could be spaces within the data in the individual cells then you can use this one-liner instead...
    Please Login or Register  to view this content.
    Last edited by Rick Rothstein; 07-14-2020 at 10:55 AM.

+ 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. Extract text from a cell without the last string after some character.
    By tomasrage in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-25-2020, 04:19 AM
  2. Add character to string in a cell
    By brent_milne in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-16-2015, 04:37 PM
  3. [SOLVED] replace nth occurrences of a character string within a cell...
    By herbie226 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-09-2014, 02:50 AM
  4. [SOLVED] find the character position in a string of the last occurrence of a nominated character
    By jmac1947 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-04-2014, 11:52 PM
  5. Replies: 8
    Last Post: 04-22-2013, 12:24 PM
  6. [SOLVED] Check if each cell contains Initials of MyString after specific character in cell string
    By jordan2322 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-12-2012, 09:45 PM
  7. Adding a character to a cell
    By rlkerr in forum Excel General
    Replies: 5
    Last Post: 10-13-2009, 08:43 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1