Replace a name that has been written backwards

  • Hi guys, I need to replace names and convert them into numbers in a excel file, I am using this line of code to do it but this is only working if the person write name and then surname, but I also need to convert it if some write first the surname and then the name, I dont know if there is a way to do it.


    For example here I can replace the name and convert it to "2" if has been written "Luigi Iacomini" but iif has been written "Iacomini Luigi" I can not do it


    Maybe someone can help me thank you.


    Code
    1. Worksheets("Anagrafica audit").Cells.replace What:="Luigi Iacomini", Replacement:="2"
    2. Worksheets("Anagrafica audit").Cells.replace What:="Luca Cinnirella", Replacement:="3"
  • I'm not sure that is possible because Excel has no way of knowing which is the first name and which is the surname. The only way I can think of to make this work would be to have a list of names you want to replace in another sheet, for example, first name in column A, surname in column B and the replacement number in column C. Could you attach a copy of your file?

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • you can be right, this is the code I have, and the attached file is one of the group I need to convert.


  • Are the names listed in your macro always the same or will they change? Names like "Pier Giorgio SPAZZINI" can also be written as "SPAZZINI Pier Giorgio". Is this correct?

    I have to go out for an appointment now. I will respond as soon as I can.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • If you have a database then have names entered separately, one column for first name and one for last name, then you can use a concatenate formula to get them in the correct order.

  • Quote

    Right now, I have done a Data Base with the names, surnames and the corresponding code, this can be very helpful.

    If you have a Database then it should control how names are entered. Also, it should generate a unique identifier.


    I've just looked at you example workbook and it nothing like a database. It looks like a collection of forms which is the wrong way to store data.

  • You can control how the names are entered using a data input form. The actual data sheet could be hidden to prevent manual entry.


    See my free database form example - there's a link in my signature below.

  • It looks like the names you want to replace are in sheet "Anagrafica audit", range A12:A17. If this is correct, can this range change or does it remain constant?

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • For laboratories, the number N.LAT (this is always in the Anagrafica Adit in cell B4) is the one I can change for the corresponfing code.

    For inspectors, the names can be in sheets "Anagrafica audit" and "report stampabile" and the range can change

  • It looks like the inspectors in "report stampabile" are chosen from a drop down list which refers to A12:A17 in "Anagrafica audit". When you say: "the range can change" - do you mean it could be A12:A20 or A12:A100?

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Your current code looks for the names to replace in any cell in the sheet. Are the names you want to replace in A12:A17 or can they also be in A21:A30 (the ranges are those in the current sheet)? Your data base has 77 names. Will you be replacing all those names if they exist or only some of the names? If only some, how do you determine which names to replace?

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • The names and laboratories I have wrote in the code are some of the DataBase, this was the code I made before having the complete Database with all the names and laboratories I need to codified, The idea is replace all the names and laboratories if they exists.

  • Please answer this question:

    Are the names you want to replace in A12:A17 or can they also be in A21:A30 (the ranges are those in the current sheet)?

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.