Excel/reorder and truncate text in cell
QUESTION: cell a1 has text as such: "My name is P.Emrich"
a2 is "My friend's name is A.Heritage"
I want to be able to replace the text in the cells with just "Emrich,P"
Can you suggest a macro to just get the last name followed by a comma and the initial?
ANSWER: Providing that the rule is always that the name is at the end, then you COULD do it with a formula
by putting this in a blank column, you will get the value you want - you could then copy and paste special, values over the existing data. This has the advantage of displaying the data you are going to use before destroying the original - which a macro would do.
A macro using the same method would be
For Each cell In Range("A1:a2")
cell.Value = WorksheetFunction.Substitute(Mid(cell.Value, WorksheetFunction.Search(".", cell.Value) - 1, 2) & Right(cell.Value, Len(cell.Value) - WorksheetFunction.Search(".", cell.Value)), ".", ",")
replace my a1:a2 with the actual range you are testing.
---------- FOLLOW-UP ----------
QUESTION: Thanks Aidan, but the formula gives me P,Emrich not Emrich,P
Did not test the macro.
Sorry, blame it on the long day here in the UK coupled with a 4 month old - try
for the formula method - amend the macro in a similar way (let me know if this isn't clear though!)