Group Names Together

  • Hi,


    I hope someone can help.


    The best way of describing my problem is this:


    I want to be able to have a set of names replaced by one singular name, e.g.


    Hilton monkeys
    hilton monkeys on top
    Hilton Power
    Hilton jimmy
    Hilton jimmy on top
    Hilton jimmy on top with bananas
    Marriott jimmy
    Marriott jimmy on top


    (ofcourse these are made up!). What I would like is to group the names to simply 'Hilton' and 'Marriott'. There must be a way of doing this, so I don't have to select all the separate cell numbers. Is there a simple way. I have tried using replace in Excel and that doesnt allow me to do it. Macros must do, but I'm trying to avoid having to write 5000 scripts selecting each particular cell. Is there any way of doing this?


    Surely the 'replace' function should do it with some key character like replace hilton * with Hilton??? Thats all I want, but there doesnt appear to be that option. So anyone have any ideas?


    would be very grateful to hear from you.


    Regards,


    Greg Allen

  • Re: Replacing sets of names- there must be any easy way


    I'm sure. It will only replace them one at a time, I wanted to use macros to enable to me replace large lists. I think the script would be something like this:


    As the sheet contains so many of these different variations, I would like to be able to write something that would replace all words starting with hovis.... to hovis. and same for various others names. Is there such a way?

  • Re: Replacing sets of names- there must be any easy way


    Hi,


    This bit of code replaces text with the first complete word from the text. Hope it does what you want - may need some more error trapping in place.



    Regards,


    Q

  • Re: Replacing sets of names- there must be any easy way


    How are these data arranged in the cells of your spreadsheet? If each name is in a cell and you want each cell to end up with just the first word, you can use


    =LEFT(TRIM(A1)&" ",FIND(" ",TRIM(A1)&" "))

  • Re: Replacing sets of names- there must be any easy way


    Thank you for your help.


    Both these reply 'Compile errors' when I try them, I am not huge;u familar with macros script writing- infact i only starting tryint to write it myself yesterday.


    Replace does actually work, but because the lists (or more precisely the variables of names) are very large (5000), I would like to create a button on the spreadsheet that I can then press and it will change all the names to the first word (e.g. Hilton Breaks = Hilton, Marriott Breaks= Marriott). I just want to understand how to write the replace script in macros so I can replicate it over all the various first names.

  • Re: Replacing sets of names- there must be any easy way


    Hi,


    Quote

    Surely the 'replace' function should do it with some key character like replace hilton * with Hilton??? Thats all I want, but there doesnt appear to be that option. So anyone have any ideas?


    Hit Ctrl+H


    find what: " *" without quotes
    Replace with: leave this empty
    Replace all.

  • Re: Replacing sets of names- there must be any easy way


    Greg, something like this doesn't require singular scripts and is easy to do with a macro provided we know what data, ranges etc. In fact you could do it with out code if I understand you correctly.


    Is is possible to upload a rough work book ?

Participate now!

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