How to reverse the first name and last name

caro3312 Posted messages 97 Status Member -
Gaelle - 22 Jun 2019 at 12:08

Hello,

I have a file with column A that includes the FIRST NAME and the LAST NAME.
However, the first name is before the last name, whereas I would like the opposite.
I have used formulas that included “=RIGHT(A1;LEN..... etc., but sometimes there are multiple first names, so it doesn’t work very well.
The trick would be to use the uppercase letters. Indeed, the last name is in uppercase, and the first name is in lowercase (except for the first letter, of course).
Is there a formula that could reverse this based on the last name being in uppercase?

Thanks in advance!
Caro

6 answers

Answer 1 / 6

Best answer
The best answer is the one that has been validated by our teams.
Boisgontierjacques Posted messages 177 Status Member 64

Good evening,

http://boisgontierjacques.free.fr/fichiers/Matriciel/MatNomPrenom2.xls

Boisgontier
8
1. Mouftie Posted messages 234 Status Member 17
  
  A big thank you for all your work for the community.
  
  0
2. Gaelle
  
  Awesome this file! Thank you very much!
  
  0
Answer 2 / 6
leberry76 Posted messages 78 Status Member 62

Hello,
I will try this:
In column B: =LEFT(A1;SEARCH(" ";A1;1)-1)
In column C: =RIGHT(A1;(LEN(A1)-SEARCH(" ";A1;1)))
In column D: =B1&" "&C1
Then I will copy and paste column D and put it on a new Excel sheet to have the raw data (and delete the 3 other working columns).

The only downside is names or first names that are compound with a space...
4
1. caro3312 Posted messages 97 Status Member 1
  
  Hello again,
  
  I just tried, but it indeed doesn't work because there are sometimes two first names.
  Example from my file:
  
  Albert Bastien LASTNAME
  
  So, it would be about finding a formula that would convert what is written in uppercase to lowercase, and if possible directly in a single cell...
  
  It would result in
  LASTNAME Albert Bastien
  
  Thanks in advance!!!
  
  1
Answer 3 / 6
caro3312 Posted messages 97 Status Member 1

Thank you! I'll try that right away!
However, to understand correctly, could you explain the formula to me?
LEN;SEARCH? why -1?
Thank you!!!
0
1. leberry76 Posted messages 78 Status Member 62
  
  SEARCH: We search for the position of the space character in cell A1 starting from character 1.
  SEARCH (search string; cell to search; starting character for the search)
  FOR EXAMPLE A1: DUPONT CHRISTIANE
  SEARCH(" ";A1;1) => 7
  
  LEFT (source string; n): extracts the first n characters: In our example, we see that we want the first 6 characters, so the result of search -1.
  
  RIGHT(source string; n) extracts the last n characters. We need to determine n n = Length of the source string - the number of characters up to and including the space, that is
  LEN(A1)-SEARCH(" ";A1;1)
  
  We base it on the space, which is why the only concern will be composite names or first names...
  
  0
2. leberry76 Posted messages 78 Status Member 62
  
  For more precision:
  
  RIGHT and LEFT functions
  http://www.mdf-xlpages.com/modules/publisher/item.php?itemid=81
  
  SEARCH and FIND functions
  http://www.mdf-xlpages.com/modules/publisher/item.php?itemid=93
  
  0
  1. Raymond PENTIER Posted messages 58213 Registration date Status Contributor Last intervention 17 482 > leberry76 Posted messages 78 Status Member
    
    and what about the uppercase letters of the name?
    
    0
  2. leberry76 Posted messages 78 Status Member 62 > Raymond PENTIER Posted messages 58213 Registration date Status Contributor Last intervention
    
    I don't understand? For my part, we can't define in Excel where uppercase letters stop and where lowercase letters start... Moreover, the source is also wrong since if there are two first names, there are two uppercase letters...
    
    For me, it's not feasible, but that's just my opinion... Maybe via VBA...
    
    0
3. caro3312 Posted messages 97 Status Member 1
  
  Hello,
  
  Thank you for all your responses. What I did was the famous Right formula, etc... and if I found, for example:
  Albert LASTNAME Antoine
  I would cut and paste the name to place it in front...
  Fortunately, there weren't too many.
  
  In any case, thanks to everyone!
  
  0
Answer 4 / 6
tontong Posted messages 2575 Registration date Status Member Last intervention 1 064

Hello,
Jacques Boisgontier proposes 2 matrices to solve this problem:
http://boisgontierjacques.free.fr/pages_site/chaines.htm#FormMat
0
Answer 5 / 6
ccm81 Posted messages 11033 Status Member 2 434

Hello everyone

A solution via a macro (vba)
http://www.cjoint.com/c/GEjs65eeZUM

Best regards
0
1. caro3312 Posted messages 97 Status Member 1
  
  Hello,
  
  I didn't find the property, but thanks anyway!!
  
  Caro
  
  0
Answer 6 / 6
Raymond PENTIER Posted messages 58213 Registration date Status Contributor Last intervention 17 482

And why didn't you use the method of Jacques Boisgontier, who is the great specialist in advanced formulas on Excel, and who took the time to personally respond to you?
Moreover, this method perfectly matches your request!

--
It's nice, retirement! Especially in the Caribbean... :-)
Raymond (INSA, AFPA, CF/R)
0
1. Caro3312
  
  Hello,
  I hadn't received all the replies (not displayed on my phone).
  But are you talking about the Excel file including first name last name, then last name, then first name, and finally the famous name and first name?
  Indeed, I wanted to avoid creating new columns, knowing that the file I have was created differently (not all the columns are next to each other, and I don't have a good grasp of the software).
  But I am still trying to understand its formula; what do the numbers like 999 etc. correspond to?
  
  I have other files with the same issue, so I will apply its formula!
  
  Thank you all!
  
  0
2. ccm81 Posted messages 11033 Status Member 2 434
  
  Indeed, I wanted to avoid creating new columns, knowing that the file I have was created differently (not all the columns are next to each other, and I don't master the software)
  Have you tried the macro proposed earlier? It doesn't require adding a column
  
  Best regards.
  
  0

How to reverse the first name and last name

6 answers

Smart tech tv without remote control

How to reset a gps on a 2010 automatic c4 picasso clocking?

I no longer have the scrollbar in the right margin

Doctolib plans to use your health data for its ai research

Google maps does not recognize my current location [pc]

Understanding the use of the 'i' compartment in the washing machine

No network inside the house.

Installation of the claude ai

Missing lock icon

Texture issues in enshrouded