View original (French)

Keep only the numbers or letters from a cell

Solved
arguhz -  
arguhz Posted messages 1 Registration date   Status Member Last intervention   -
Hello,
I have a problem that seems simple but I'm stuck.
I have a table from which I want to extract part of each cell in another sheet based on its content.

I have managed to simplify my problem into 3 cases:
- if the cell contains only letters: keep the content
- if the cell contains only numbers: keep the content
- if the cell contains letters + numbers + possibly a dash or a space: keep only the numbers.

Knowing that the numbers are always on the left and the letters on the right in the last case. I tried a formula with "if" statements nested within each other, but I wasn't successful. I would ideally like a formula that I could easily apply to the entire table while targeting the same cell in another sheet.

Does anyone have a lead for me?

Configuration: Windows / Firefox 45.0

3 answers

  1.
    Raymond PENTIER Posted messages 58211 Registration date   Status Contributor Last intervention   17 480
     
    You will be offered a suggestion when you have provided examples of mixed content (all possible cases).
    The solutions will either involve the LEFT and RIGHT functions or the SUBSTITUTE function.

    --
    Retirement is great! Especially in the West Indies... :-)
    Raymond (INSA, AFPA, CF/R)
    1
  2. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
     
    Hello
    try this one for A1
    be careful, don't forget anything, it's a bit long!!
    =IF(LEFT(A1,LEN(A1)-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,1,"");2,"");3,"");4,"");5,"");6,"");7,"");8,"");9,"");0,""))) ="" ,A1,LEFT(A1,LEN(A1)-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,1,"");2,"");3,"");4,"");5,"");6,"");7,"");8,"");9,"");0,"")))*1)

    this is what it should give
    https://mon-partage.fr/f/GmdzeiAo/

    Note that as the formula is written, the result is numeric when there are numbers, and thus if the text starts with 0, it will not appear.
    to eliminate this if needed, remove the final *1

    regards

    ps actually we can shorten a little at the beginning:
    =IF(LEN(A1)-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,1,"");2,"");3,"");4,"");5,"");6,"");7,"");8,"");9,"");0,""))=0,A1,LEFT(A1,LEN(A1)-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,1,"");2,"");3,"");4,"");5,"");6,"");7,"");8,"");9,"");0,"")))*1)
    The quality of the response largely depends on the clarity of the question, thank you!
    0
  3. arguhz Posted messages 1 Registration date   Status Member Last intervention  
     
    Thank you for the quick responses,
    I ended up using the following formula which seems to work:
    =IF(ISNUMBER(1*LEFT(G6))=FALSE,G6,IF(ISNUMBER(1*RIGHT(G6)),1*G6,IF(ISNUMBER(1*LEFT(G6,2)),1*LEFT(G6,2),1*LEFT(G6,1))))

    I am testing the different parts with the ISNUMBER() function. First, the first character on the left, then the one on the right. Finally, I check if the first two characters form a number; otherwise, I conclude that there is only one. I'm betting everything on the fact that the numeric part only contains 1 or 2 digits; otherwise, it doesn't work.
    0