View original (French)

Add a space before a capital letter

Solved
Christouf1542 Posted messages 15 Status Member -  
Christouf1542 Posted messages 15 Status Member -
Hello,

I searched the forum but couldn't find the right formula.

I have a column that looks like this:
AlexandreDurand
PaulDurier
MaximeNicier
AlphonseMercier
PierreNicolasDurantet

I would like to add a space before the capital letters but without creating a space at the beginning of the cell.

Is there a formula for that?

Thank you very much!!!

3 answers

  1.
    m@rina Posted messages 27471 Registration date   Status Moderator Last intervention   11 562
     
    Hello

    I don't see how to do this with an Excel formula...
    Do you have a recent version of Excel, meaning at least 2013?
    If so, no need for a formula, it's very simple:

    - Insert a column to the right of your name column
    - In front of the first name, type Alexandre (to show Excel the example)
    - Then, select the entire empty column down to the same level as the filled column
    - Under the Data tab, Data Tools group, click on Flash Fill => that's it for the first names
    - Insert a new column to the right and do the same for the last name

    Then you can do a concatenation.

    However, compound first names might pose a problem, but there is a way to recover the middle names and then associate them with the first name.

    Another perhaps even simpler solution is to use Word and there, no problem for compound names:
    - Copy your column and paste it into Word
    - Open the Find-Replace dialog box
    - Check "Use wildcards"
    - Fill it in like this:
    Find: ([a-z])([A-Z])
    Replace with: \1 \2
    And that's all good
    You just have to copy and paste from Word back to Excel

    NB: there is indeed a space between \1 and \2

    m@rina

    --
    Do not ask me your questions privately. I will not answer them.
    1
    1. tontong Posted messages 2575 Registration date   Status Member Last intervention   1 064
       
      Hello,
      We can use a similar approach with Calc instead of Word.
      Search: ([a-z])([A-Z])
      Replace with: $1 $2
      Make sure to check "Regular expressions" in the Options of the dialog box.
      0
  2. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
     
    Hello
    with a formula, for a text in A1, you can always write:

    =TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"A"," A"),"B"," B"),"C"," C"),"D"," D"),"E"," E"),"F"," F"),"G"," G"))..........
    to be completed with all the letters of the alphabet and as many SUBSTITUTE at the beginning, behind the TRIM
    ... just a bit long to enter
    kind regards

    --
    The quality of the response mainly depends on the clarity of the question, thank you!
    0
  3. Christouf1542 Posted messages 15 Status Member
     
    Thank you very much to everyone!!!! The different solutions work very well :)!
    0