Fix names with code

**mdbct** · 02-17-2010, 12:45 AM

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** · 02-17-2010, 12:53 AM

mdbct,
The code worked OK,
Thanks,
Xrull

**DonkeyOte** · 02-17-2010, 02:37 PM

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** · 02-17-2010, 02:48 PM

I missed this bit...

Originally Posted by mdbct

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)