Correct name

[shimaa01234]

I have a Group of names I want to correct names
So that the first letter is the last
The last letteris the first letter
Such an example

retep	Peter
oarcP	Marco
ntefanS	Stefan

<tbody>
</tbody>

 

[shimaa01234]

But you can compile all the names without a second Formula I think will be better

 

[Vidar]

Here's a single cell formula you can try. It works with 1, 2 or 3 names in a single cell, seperated by a single space.

=CHOOSE(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1,RIGHT(A1,1)&MID(A1,2,LEN(A1)-2)&LOWER(LEFT(A1,1)),MID(A1,SEARCH(" ",A1)-1,1)&MID(A1,2,SEARCH(" ",A1)-3)&LEFT(A1,1)&" "&RIGHT(A1,1)&MID(A1,SEARCH(" ",A1)+2,LEN(A1)-SEARCH(" ",A1)-2)&MID(A1,SEARCH(" ",A1)+1,1),MID(A1,SEARCH(" ",A1)-1,1)&MID(A1,2,SEARCH(" ",A1)-3)&LEFT(A1,1)&" "&MID(A1,SEARCH("ZZZZZ",SUBSTITUTE(A1," ","ZZZZZ",2))-1,1)&MID(A1,SEARCH(" ",A1)+2,SEARCH("ZZZZZ",SUBSTITUTE(A1," ","ZZZZZ",2))-SEARCH(" ",A1)-3)&MID(A1,SEARCH(" ",A1)+1,1)&" "&RIGHT(A1,1)&MID(A1,SEARCH("ZZZZZ",SUBSTITUTE(A1," ","ZZZZZ",2))+2,LEN(A1)-2-SEARCH("ZZZZZ",SUBSTITUTE(A1," ","ZZZZZ",2)))&MID(A1,SEARCH("ZZZZZ",SUBSTITUTE(A1," ","ZZZZZ",2))+1,1))

 

[shimaa01234]

This is actually a wonderful solution which is required Thank you very much Mr "Vidar " for your effort, and your time But you can increase the number of names from "3" to a larger number of names

 

[Vidar]

You're welcome .
It's possible, but the formula get increasingly longer and more complex. I would suggest using VBA.

 

[Rick Rothstein]

It's possible, but the formula get increasingly longer and more complex. I would suggest using VBA.

Just pointing out for the OP's consideration... the UDF solution I posted in Message #9 has no limit on the number of names in the value passed into it (you can have one, two, three, four, five, etc. names).

 

[shimaa01234]

Yes mister "Rick" I know this very well But I wish you could make the formula for me Because I know you are a genius in everything " code & formula" Mister "Rick" I am very impressed with your work, and I retain them all

 

[shimaa01234]

Mister "Vidar" I'm sorry again, but I wanted to do so by the formula

 

[Vidar]

No need to be sorry . If VBA is not an option then I suggest you first split the names in the cells by using the "Text to Columns" Data tool in Excel.
Choose "Space" as the delimiter. It will split the names into seperate cells. Then you can use the formula to switch the first and the last letter of the name in each cell.

 

[Rick Rothstein]

Yes mister "Rick" I know this very well But I wish you could make the formula for me

The problem is that Excel was designed as a number cruncher and it string handling functions are somewhat limited. In order to develop a single formula solution, we would need to know them maximum number of names you would ever have in a single cell. If too many, then a single formula solution would be so large as to not be worth developing (that is one of the reasons why VBA is there). Now, you said three is not enough... I am thinking three would be a practical maximum myself... as Vidar told you, "It's possible, but the formula get increasingly longer and more complex" as more names have to be handled. So, given that, what is the maximum number of names you would need to be able to handle?

 

[shimaa01234]

Thank you mister "Vidar", again I understand the situation well