Adding a char to another number entries with several 'alt enter' space in a cell

  • Hello sir!


    I think this is my very 1st post in this forum. Previously I was just a 'lurker', reading and getting solutions to most of my Excel problems, thanks to all of you! But now, here is one problem which I could not find it either here or on any Google search links. Here it goes:


    Supposed that I have a cell, say A1, which contains some numbers with multiple 'alt enter' space in it, like this:
    123
    73


    23




    8532


    452


    Notice that the gap between the numbers varies between one another, depending on how many 'alt enter' space is used. My aim is to add a single character or number just immediately after the current keyed number while still mantaining the gap between them. So for the first example, say i would like to add a letter 'a' next to each of the numbers, the expected output would be:
    123a
    73a


    23a




    8532a


    452a


    or, if I would add number '7' next it each of it, it will be like this:
    1237
    737


    237




    85327


    4527


    Is it really possible to do so? It doesn't matter if the formula will be very long and complex. I prefer the Non-VBA way but if using VBA is much easier, I can still consider it.


    Thank you so much for any help.

  • Re: Adding a char to another number entries with several 'alt enter' space in a cell


    use subtitue formula:


    =SUBSTITUTE(A1,CHAR(10),7) where 7 being what you want to replace this with

  • Re: Adding a char to another number entries with several 'alt enter' space in a cell


    Hello MrRedli,


    Thanks for the reply. I've tried using your answer, but it does not produce the output I desire. It becomes like this:


    12377377237777853277452


    I believe this is because that there are multiple 'alt-enter' space inbetween the numbers, so it substitute them as well. What I would like to do is to add a single character right next to the number, while still mantaining the same gap (the same 'alt-enter' space). Thanks for the suggestion anyway.


    If there's anyone else can suggest any solution, I would be very grateful.


    Thanks!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!