Fix names with code

[Xrull]

Good day,
I have a spread sheet where I've got to change the names, eg The name Paul Dechristian with this formula:

=TRIM(RIGHT(B2,LEN(B2)-FIND(" ",B2))&", "&LEFT(B2,FIND(" ",B2)-1))

would be Dechristian, Paul.
I tried using a code Donkeyote used to solved a problem on this forum:

Sub Example()
With Range(Cells(1, "B"), Cells(Rows.Count, "B").End(xlUp))
    .Value = Evaluate("IF(ISTEXT(" & .Address & "),PROPER(SUBSTITUTE(" & .Address & ","" "","", "",1)),REPT(" & .Address & ",1))")
End With
End Sub

I changed it to this:

Sub Example1()
With Range(Cells(1, "B"), Cells(Rows.Count, "B").End(xlUp))
    .Value = Evaluate("IF(ISTEXT(" & .Address & "),TRIM(RIGHT(" & .Address & ",LEN(" & .Address & ")-FIND("" "",& .Address)),REPT(" & .Address & ",1))")
End With
End Sub

but I get #VALUE! for all the cells.
What am I doing wrong?
Thanks,
Xrull

[mdbct]

Here is the Exapmle1 from below.

Sub Example1()
With Range(Cells(2, "B"), Cells(Rows.Count, "B").End(xlUp))
    .Value = Evaluate("if(istext(" & .Address & "),trim(right(" & .Address & ",len(" & .Address & ")-find("" ""," & .Address & "))),rept(" & .Address & ",1))")
End With
End Sub

this part

-FIND("" "",& .Address)),REPT(" & .Address & ",1))"

should be

-find("" ""," & .Address & "))),rept(" & .Address & ",1))"

[Xrull]

mdbct,
The code cuts off the first name, it doesn't change Paul Dechristian to Dechristian, Paul.
Thanks,
Xrull

[mdbct]

That's what the formula in the Example1 that you posted was set up to do. I didn't change the base formula in Example1.

[mdbct]

Try this one

Sub Example1()

Set myrange = Range(Cells(2, "B"), Cells(Rows.Count, "B").End(xlUp))
For Each ccell In myrange
    With ccell
    .Value = Evaluate("trim(right(" & .Address & ",len(" & .Address & ")-find("" ""," & .Address & "))& "", "" & Left(" & .Address & ",find( "" ""," & .Address & ")-1))")
    End With
Next

End Sub

I tried using the same method

Sub Example2()
With Range(Cells(2, "B"), Cells(Rows.Count, "B").End(xlUp))
    .Value = Evaluate("trim(right(" & .Address & ",len(" & .Address & ")-find("" ""," & .Address & "))& "", "" & Left(" & .Address & ",find( "" ""," & .Address & ")-1))")
End With
End Sub

This method didn't work. For some reason all cells were duplicates of cell B2.

[Xrull]

mdbct,
The code worked OK,
Thanks,
Xrull

[DonkeyOte]

FWIW, using formula based approach of:

=MID(B2&", "&B2,FIND(" ",B2)+1,LEN(B2)+1)

applied to Range simultaneously via Evaluate

Sub Example()
With Range(Cells(2, "B"), Cells(Rows.Count, "B").End(xlUp))
    .Value = Evaluate("IF(ISTEXT(" & .Address & "),MID(" & .Address & "&"", ""&" & .Address & ",FIND("" ""," & .Address & ")+1,LEN(" & .Address & ")+1),REPT(" & .Address & ",1))")
End With
End Sub

[DonkeyOte]

I missed this bit...

I tried using the same method

Sub Example2()
With Range(Cells(2, "B"), Cells(Rows.Count, "B").End(xlUp))
    .Value = Evaluate("trim(right(" & .Address & ",len(" & .Address & ")-find("" ""," & .Address & "))& "", "" & Left(" & .Address & ",find( "" ""," & .Address & ")-1))")
End With
End Sub

This method didn't work. For some reason all cells were duplicates of cell B2.

The initial ISTEXT test in the earlier example(s) actually serves a dual purpose

1 - as implied it allows us to differentiate between Text and Non-Text entries such that we can apply an appropriate function to underlying value

2 - it crucially creates an array of Boolean outputs - one per cell referenced in the range.
It is this array of Booleans that permits us to essentially run the Evaluate over the range on a cell by cell basis - conducting an IF on each cell and acting accordingly (True,False).

Without the ISTEXT the Evaluate will be conducted against the first cell in the range and result applied to entire range.

More commonly you see

Evaluate("IF(ROW(" & .Address & "),somefunction)")

ie ROW is used to generate numeric values as the IF test (only 0 is FALSE of course) ... however here it makes sense to use an actual ISTEXT test given we want to process differently
(ie we don't want to apply our formula to numeric cells etc)

[mdbct]

Thanks for the explanation.

[DonkeyOte]

No problem.

A far better explanation of the ISTEXT/ROW approach can be seen at the following link:

http://www.mrexcel.com/forum/showpos...0&postcount=13

Kudos as always to the author, PGC01 - responsible for countless jaw dropping solutions.