View original (French)

LibreOffice Calc: extract part of a cell

Solved
barnabe0057 Posted messages 14329 Registration date   Status Contributor Last intervention   -  
Francois78000 Posted messages 2 Status Member -
Hello,

I'm looking for a way to extract part of a cell value, let me explain:

In a spreadsheet, I have a column containing values like l+k://coordinates?16418,15949&20
the numbers 16418 and 15949 represent x and y coordinates, I would like to extract them and put them next door in two separate columns named x coordinate and y coordinate.

I'm using LibreOffice 3.6.5.2

Thank you in advance for your help.

Configuration: Win 7 Pro 64bits
Athlon 4800+ Dual Core
2 GB DDR2 800 MHz

4 answers

  1.
    tontong Posted messages 2575 Registration date   Status Member Last intervention   1 064
     
    Hello,
    According to the constant elements, the formulas will be more or less long.
    Example =MID(A2,19,5) returns the 5 characters following the 19th, so 16418.
    Which elements are constant? Which elements vary?
    8
    1. Francois78000 Posted messages 2 Status Member
       
      Hello Tontong,
      I have a similar problem to Barnabe0057, except that I don't have the same constant elements:
      /formation-petite-enfance-Vieux-port-27680
      /formation-petite-enfance-Villaines-la-juhel-53700

      The constant element is the 5 digits that are always at the end of the line,
      I would like to extract these 5 digits (they are always 5) at the end of the line and copy them to the next column.
      0
      1. tontong Posted messages 2575 Registration date   Status Member Last intervention   1 064 > Francois78000 Posted messages 2 Status Member
         
        Hello,
        Just enter the formula =RIGHT(A1,5) in B1 and drag down.
        If an unwanted space is disrupting the text at the beginning or end, use the formula:
        =RIGHT(TRIM(A1),5)
        1
      2. Francois78000 Posted messages 2 Status Member > tontong Posted messages 2575 Registration date   Status Member Last intervention  
         
        Thank you very much, it works perfectly!
        0
  2. barnabe0057 Posted messages 14329 Registration date   Status Contributor Last intervention   4 930
     
    Comment automatiser votre formule sur l'ensemble de ma colonne ?

    Parce que je ne me vois pas taper la formule 10 000 fois :
    B2=RIGHT(A2, 5)
    B3=RIGHT(A3, 5)
    B4=RIGHT(A4, 5)
    etc ...
    2
    1. tontong Posted messages 2575 Registration date   Status Member Last intervention   1 064
       
      Using the fill handle: it's the small black square at the bottom right of the cell.
      Hover the pointer over it, hold down the left click, and drag down.
      If the adjacent column to the left contains values, you can also double-click on this fill handle.
      0
    2. barnabe0057 Posted messages 14329 Registration date   Status Contributor Last intervention   4 930
       
      Okay, I have to step out, I'll try that tonight.

      I'll keep you updated.
      0
  3. barnabe0057 Posted messages 14329 Registration date   Status Contributor Last intervention   4 930
     
    Thank you for your response, the length of the values is fixed, and the only elements that vary in these values are the two numbers corresponding to the coordinates.

    So your formula is perfect ;)
    0
  4. barnabe0057 Posted messages 14329 Registration date   Status Contributor Last intervention   4 930
     
    It works very well, thank you so much for your valuable help!

    Have a great day, see you!
    0