Replace a name that has been written backwards

Paulandreaoc · May 19th 2020

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

Worksheets("Anagrafica audit").Cells.replace What:="Luigi Iacomini", Replacement:="2"
Worksheets("Anagrafica audit").Cells.replace What:="Luca Cinnirella", Replacement:="3"

Mumps · May 19th 2020

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?

Paulandreaoc · May 19th 2020

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

Code

Sub replace()

Const str As String = "PERSONE COINVOLTE DEL CAB"
Dim rCl As Range, rRng As Range
Dim Rws As Integer
Set rCl = ActiveSheet.UsedRange.Find(str, LookIn:=xlValues, lookat:=xlWhole)
Set rCl = rCl.Offset(2)
Rws = Range(rCl, rCl.Offset(, 1).End(xlDown)).Rows.Count
Set rRng = rCl.Resize(Rws, 3)
rRng.ClearContents

    For Each Sheet In ActiveWorkbook.Worksheets
        If Sheet.Name = "Giudizio sintetico" Then
            Application.DisplayAlerts = False
            Sheets("Giudizio sintetico").Delete
            Application.DisplayAlerts = True
        End If
    Next
    For Each Sheet In ActiveWorkbook.Worksheets
        If Sheet.Name = "Riepilogo" Then
            Application.DisplayAlerts = False
            Sheets("Riepilogo").Delete
            Application.DisplayAlerts = True
        End If
    Next
        
    Worksheets("Anagrafica audit").Cells.replace What:="046" And "46", Replacement:="A"
    Worksheets("Anagrafica audit").Cells.replace What:="023" And "23", Replacement:="B"
    Worksheets("Anagrafica audit").Cells.replace What:="074" And "74", Replacement:="C"
    Worksheets("Anagrafica audit").Cells.replace What:="206", Replacement:="D"
    Worksheets("Anagrafica audit").Cells.replace What:="084" And "84", Replacement:="E"
    Worksheets("Anagrafica audit").Cells.replace What:="Claudio Pasin", Replacement:="1"
    Worksheets("report stampabile").Cells.replace What:="Claudio Pasin", Replacement:="1"
    Worksheets("Anagrafica audit").Cells.replace What:="Luigi Iacomini", Replacement:="2"
    Worksheets("report stampabile").Cells.replace What:="Luigi Iacomini", Replacement:="2"
    Worksheets("Anagrafica audit").Cells.replace What:="Luca Cinnirella", Replacement:="3"
    Worksheets("report stampabile").Cells.replace What:="Luca Cinnirella", Replacement:="3"
    Worksheets("Anagrafica audit").Cells.replace What:="Fabrizio MANTA", Replacement:="4"
    Worksheets("report stampabile").Cells.replace What:="Fabrizio MANTA", Replacement:="4"
    Worksheets("Anagrafica audit").Cells.replace What:="Paola PEDONE", Replacement:="5"
    Worksheets("report stampabile").Cells.replace What:="Paola PEDONE", Replacement:="5"
    Worksheets("Anagrafica audit").Cells.replace What:="Pier Giorgio SPAZZINI", Replacement:="6"
    Worksheets("report stampabile").Cells.replace What:="Pier Giorgio SPAZZINI", Replacement:="6"
    Worksheets("Anagrafica audit").Cells.replace What:="Giagnorio Annunziata", Replacement:="7"
    Worksheets("report stampabile").Cells.replace What:="Giagnorio Annunziata", Replacement:="7"
    Worksheets("Anagrafica audit").Cells.replace What:="Aragone Gianni", Replacement:="8"
    Worksheets("report stampabile").Cells.replace What:="Aragone Gianni", Replacement:="8"
    Worksheets("Anagrafica audit").Cells.replace What:="Giorgio Melis", Replacement:="9"
    Worksheets("report stampabile").Cells.replace What:="Giorgio Melis", Replacement:="9"
    Worksheets("Anagrafica audit").Cells.replace What:="Annarita Lazzari", Replacement:="10"
    Worksheets("report stampabile").Cells.replace What:="Annarita Lazzari", Replacement:="10"
    Worksheets("Anagrafica audit").Cells.replace What:="Gian Bartolo Picotto", Replacement:="11"
    Worksheets("report stampabile").Cells.replace What:="Gian Bartolo Picotto", Replacement:="11"
    
    
    Worksheets("Anagrafica audit").Range("b3:c3").UnMerge
    Worksheets("Anagrafica audit").Range("b3").ClearContents
    Worksheets("Anagrafica audit").Range("b5:c5").UnMerge
    Worksheets("Anagrafica audit").Range("b5").ClearContents
    Worksheets("report stampabile").Range("p14:s14").UnMerge
    Worksheets("report stampabile").Range("p14").ClearContents
    Worksheets("report stampabile").Range("p24:s24").UnMerge
    Worksheets("report stampabile").Range("p24").ClearContents
    Worksheets("report stampabile").Range("p34:s34").UnMerge
    Worksheets("report stampabile").Range("p34").ClearContents
    Worksheets("report stampabile").Range("p44:s44").UnMerge
    Worksheets("report stampabile").Range("p44").ClearContents
    Worksheets("report stampabile").Range("p54:s54").UnMerge
    Worksheets("report stampabile").Range("p54").ClearContents
    Worksheets("report stampabile").Range("p64:s64").UnMerge
    Worksheets("report stampabile").Range("p64").ClearContents
    Worksheets("report stampabile").Range("p74:s74").UnMerge
    Worksheets("report stampabile").Range("p74").ClearContents
    Worksheets("report stampabile").Range("p84:s84").UnMerge
    Worksheets("report stampabile").Range("p84").ClearContents
    Worksheets("report stampabile").Range("p94:s94").UnMerge
    Worksheets("report stampabile").Range("p94").ClearContents
    Worksheets("report stampabile").Range("p104:s104").UnMerge
    Worksheets("report stampabile").Range("p104").ClearContents
    
    
    
    
    
    
End Sub

Display More

Mumps · May 19th 2020

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.

Paulandreaoc · May 19th 2020

they can change, because people can write it like: name then surname, or surname and then name

Paulandreaoc · May 20th 2020

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

**royUK** · May 20th 2020

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.

Paulandreaoc · May 20th 2020

But my problem is that I dont know how to find the name in my file and then replace it for the code in the Data Base

**royUK** · May 20th 2020

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.

Paulandreaoc · May 20th 2020

My database is this one, as I added in the code I send I need to codify laboratories and inspector´s names

**royUK** · May 20th 2020

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.

Mumps · May 20th 2020

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?

Paulandreaoc · May 20th 2020

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

Mumps · May 20th 2020

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?

Paulandreaoc · May 20th 2020

yep, can be A12:A13, A12:A21, or A12:A39 etc

Mumps · May 20th 2020

OK. Give me a bit of time and I'll see what I can do.

Mumps · May 21st 2020

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?

Paulandreaoc · May 21st 2020

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.

Mumps · May 21st 2020

Please answer this question:

Quote from Mumps

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)?

Paulandreaoc · May 21st 2020

they can be place in any cell starting from A12 and finishing before the header "PERSONE CONVOLTI DEL CAB", you can see in my code right before the header I remove all, because I don't need that part

Replace a name that has been written backwards

Participate now!

Share