View original (French)

Remove the first 2 characters in 5x700 cells

Solved
daddysnak Posted messages 974 Registration date   Status Member Last intervention   -  
tontong Posted messages 2575 Registration date   Status Member Last intervention   -
Hello,

I have a file with 10 columns and 700 rows for the years 2016 to 2020.
In the 5 columns containing values, I would like to remove the € symbol and the space that follows it (i.e., 2 characters) with a single manipulation. (This is to keep the 10 columns, by 50 rows, on A4 pages)
Is it possible? Of course it is possible... the problem is that I can't find how to do it.

Who can help me? Thank you in advance.

--
The future belongs to those who get up early!

4 answers

  1.
    tontong Posted messages 2575 Registration date   Status Member Last intervention   1 064
     
    Hello,
    The advice from patrice33740 and eriiic is, as always, very relevant.
    Ctrl+h is the Excel shortcut; Ctrl+f is the Calc shortcut; but the Edit >> Find and Replace instruction is the same.
    In Calc, the options are very rich and reading the help is not redundant.
    https://www.cjoint.com/c/JDulOjYqzwK
    2
  2. Patrice33740 Posted messages 8400 Registration date   Status Member Last intervention   1 783
     
    Hello,

    - Select entire columns / right-click / Format Cells
    - Numbers tab / Category: Number / Decimal places 2 / Ok

    --
    Sincerely
    Patrice

    No one can hold all knowledge, that's why we share it.
    0
    1. daddysnak Posted messages 974 Registration date   Status Member Last intervention   15
       
      Oops! I forgot to mention that it concerns the first 2 characters!
      € 0.670 or € 1.348 for example.
      So you need to remove € the space between € and 0 or 1.
      0
  3. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    Hello,

    Ctrl+h (replace)
    eric

    --
    By continually trying, we eventually succeed.
    So the more it fails, the more chances we have that it works. (the Shadoks)
    In addition to the thank you (yes, it happens!!!), remember to mark as resolved. Thank you
    0
    1. daddysnak Posted messages 974 Registration date   Status Member Last intervention   15
       
      Good evening Eric,

      I may have (definitely!) expressed myself poorly...

      Example of a column with 3 cells containing 3 different values: € 0.6987; € 0.5703; € 0.6988. I want to replace this with 0.6987; 0.5703; 0.6988.

      In reality, there are 5 columns with 48 different values in the 1st; 58 in the 2nd; 152 in the 3rd; 275 in the 4th; and 111 in the 5th.
      That makes 644 cells to modify!

      I would therefore like a function (or a macro?) that would allow me to change all these values by removing the first 2 characters (the € symbol and the space)
      either at once, or by column.
      0
      1. Patrice33740 Posted messages 8400 Registration date   Status Member Last intervention   1 783 > daddysnak Posted messages 974 Registration date   Status Member Last intervention  
         
        That's what Ctrl+h allows you to do!
        And if it doesn't work, have you tried changing the format as I suggested?
        0
      2. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281 > Patrice33740 Posted messages 8400 Registration date   Status Member Last intervention  
         
        Absolutely.
        If you do not select a range, your entire sheet will be processed; otherwise, you select your columns.
        The best thing to do is to copy your "€ " to paste it in the Replace section.
        That way, if it is a non-breaking space, it will be processed.
        eric
        0
    2. tontong Posted messages 2575 Registration date   Status Member Last intervention   1 064
       
      Hello,
      For calc it's ctrl+f.
      Also be careful: Search shifts the selection of a cell.
      0
  4. daddysnak Posted messages 974 Registration date   Status Member Last intervention   15
     
    Hello everyone,

    I probably didn't fully understand your suggestions, especially since tontong mentions ctrl-f and adds a caveat...

    I solved my problem this morning (early) using the following method:

    In cell K6, next to the last column (J), I put the formula:

    =RIGHT(J6,LEN(J6)-2)

    cell J6 included: € 0.6987
    the result of the formula... K6 contains: 0.6987... that's what I want.
    I copied/pasted this operation for the other 110 cells from column J to column K.
    Then, in Edit, I used paste special to replace the formulas with the values in column K.
    Finally, I moved the values from column K to column J. Result OK.

    I proceeded in the same way for the other 4 columns, replacing the
    letter J for the column.
    It took me barely longer than to explain it to you :)

    Thank you for your advice, and I'll keep the method you proposed (with the letter f) for other more individual circumstances.

    Take care and... stay home :)

    --
    The future belongs to those who wake up early!
    0