Adding a character after each string in cell

[ScabbyDog]

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?

[Haluk]

The function seems OK to me ....

[ScabbyDog]

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

[Haluk]

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

[ScabbyDog]

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?

[Haluk]

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.

[Haluk]

Having said this, your function may be revised as;

Please Login or Register  to view this content.

[sintek]

Works for me as well...
Try...

Please Login or Register  to view this content.

[ScabbyDog]

Excellent. Works now! Thanks all

[Rick Rothstein]

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.