Reordering Last Name and First Name

doesn't go

Thanks Rick
I see also that the address substitution is not necessary, when I know the address. But I found that @ substitution from an earlier post of yours quite neat so I left it in to remind me.
I must also note that when inside a function nested in a [ ] then quotes are possible. I have probably overlooked that in the past. I have probably overlooked that as a major advantage of the Evaluate(“ “) over the “shorthand” [ ] is often given as being able to …..“…build up a string, whereas you cannot do that with the [ ] …”...
In fact now that I recall, I think I noticed in a post of yours before .. and noted then the less tricky building up of the quotes in the [ ] compared with in the Evaluate(” “)
( – I was away all Summer from the computer so must brush up a bit :) )_ ….
_.....
For a [ ] you put in it what you put in a cell
For a Evaluate(“ “) you have to arrange that the evaluated string ends up looking like what you have in a cell … that can be a nightmare then with double - doubled - """" - """" & :- ) quotes and the such.. :-)

Alan

>> Now apply that one liner code in this form:
>>
>>Sub RangeEvaluate()
>> Let Range("B20:B25").Value = Evaluate(Replace("IF({1},TRIM(MID(@&"" ""&@,FIND("","",@)+1,LEN(@))))", "@", Range("A20:A25").Address))
>>End Sub

Alan... if the ranges are fixed and non-changing, then your one-liner can be "simplified" by using the alternative syntax for the Evaluate function.

Sub RangeEvaluate()
[B20:B25] = [IF({1},TRIM(MID(A20:A25&" "&A20:A25,FIND(",",A20:A25)+1,LEN(A20:A25))))]
End Sub

...."...Excel array formula in disguise..."... That is a nice description, Wiily, I like that :-)
see you in 2018

@Alan Elston
It can even simpler: just select the range and run my one liner, it will process all cells in the selected range at once. So you can use it on a single cell or with any range without having to change it. And you are right this one liner runs much faster than a macro using a loop. In fact it is a sort of Excel array formula "in disguise" :-)

I wish you the best for 2018.

Willy

Just for anyone that may not have noticed.. The beauty of that Evaluate Range type one liner is that it works over a Range of more than one cell, ( and often it works a lot quicker than a more typically seen code which would loop through that range applying at each loop a formula ).

So, as example, say the cells A20 to A25 look something like this:
Doe, Jane Q
Elston,Alan M
Rothstein, Rick
Elstein,Doc. Alaen
Halen,Wolfgang Van
Vanhaelen, Willy

Now apply that one liner code in this form:

Sub RangeEvaluate()
Let Range("B20:B25").Value = Evaluate(Replace("IF({1},TRIM(MID(@&"" ""&@,FIND("","",@)+1,LEN(@))))", "@", Range("A20:A25").Address))
End Sub

You should then see over the range B20 to B25
Jane Q Doe
Alan M Elston
Rick Rothstein
Doc. Alaen Elstein
Wolfgang Van Halen
Willy Vanhaelen

Merry Xmas Willy and Rick, and best wishes for the New Year..
Alan

@Willy,

And here is a one-liner UDF (user defined function) should anyone want to go that route...

Function Reorder(S As String) As String
Reorder = Trim(Mid(S & " " & S, InStr(S, ",") + 1, Len(S)))
End Function

@Rick Rothstein

Brillant !

Here is even a one-liner based on your formula :-)

Sub Reorder()
Selection = Evaluate(Replace("IF({1},TRIM(MID(@&"" ""&@,FIND("","",@)+1,LEN(@))))", "@", Selection.Address))
End Sub

@ Willy Vanhaelen and Sara Gonzalez,

Here is a shorter formula that will work whether there is a space after the comma or not...

=TRIM(MID(A1&" "&A1,FIND(",",A1)+1,LEN(A1)))

Here is a shorter formula which will produce the same results as the one shown in this article...

=MID(A1&" "&A1,FIND(" ",A1)+1,LEN(A1)-1)

@Sara Gonzalez

This shorter formula will do the job whether or not there is a space after the comma.

=TRIM(MID(A1,FIND(",",A1)+1,LEN(A1)))&" "&LEFT(A1,FIND(",",A1)-1)

What if my text isn't seperated by a space after the comma. For example our program generates the name as follows last,first with no space between the comma and first name. So when I use this formula it chops it up. So I would have to manually enter the space in order for the formula to work. Any quick fixes.

This seems much simpler to me;
Dim myArry() as string
myArry=split ("A1",",")
A1=myArry(1) & "," & myArry(0)