View original (French)

How to Extract the Second Line from a Cell in Excel?

Solved
Borisdu82 Posted messages 3 Status Member -  
papychipie Posted messages 14 Status Member -
I need to create filters for postal addresses where the source file has everything grouped in a single cell.
Some addresses have 2 lines before the last one containing the postal code and the city.
How can I extract everything that is only on the second line to copy it into another column?
For example, in column 1 I have these data in a cell in each row:
12 Avenue Gambetta
Appartement 14
82000 Montauban

I succeeded in copying "12 Avenue Gambetta" into column 2 and "82000 Montauban" into column 4 but not "Appartement 14" into column 3...
Thank you in advance for your help.
Best regards.

2 answers

  1.
    Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
     
    Hello
    if you actually have line breaks in Excel format (that is to say equal to CHAR(10), the formula for text in A1:

    =MID(A1;FIND(CHAR(10);A1);FIND("?";SUBSTITUTE(A1;CHAR(10);"?";2)-FIND(CHAR(10);A1))

    otherwise come back, but please drop some examples of your texts here:
    http://mon-partage.fr
    and come back to paste the link created on the site
    looking forward to hearing from you
    best regards
    --
    The quality of the response depends mainly on the clarity of the question, thank you!
    1
    1. Borisdu82 Posted messages 3 Status Member 1
       
      It works!!!
      Thank you very much!
      Best regards.
      1
    2. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
       
      No problem
      I consider the subject resolved
      Safe travels
      Regards
      0
  2. papychipie Posted messages 14 Status Member 2
     
    Hello Borisdu82,

    Here is a formula that might suit you:

    https://mon-partage.fr/f/jCWnXuT8/

    Best regards.
    papychipie
    0