• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extract only text

Status
Not open for further replies.
@bosco_yip
I love the lateral thinking to get number formatting to sort numbers from characters.
My formulas look different simply because that is the way I program and anything else would look out of place in my workbooks but, to recover the digits (a variant of your formula)
Code: 
= LET(
    chrs, Explodeλ(string),
    digits, TEXT(chrs, "0;;0;"),
    VALUE(CONCAT(digits))
  )
whereas to eliminate digits along with other non-letters, a different trick
Code: 
= LET(
    chrs, Explodeλ(string),
    isNum, EXACT(UPPER(chrs),LOWER(chrs)),
    CONCAT(FILTER(chrs, NOT(isNum)))
  )
 
Peter Bartholomew said:
@bosco_yip
I love the lateral thinking to get number formatting to sort numbers from characters.
My formulas look different simply because that is the way I program and anything else would look out of place in my workbooks but, to recover the digits (a variant of your formula)
Code: 
= LET(
    chrs, Explodeλ(string),
    digits, TEXT(chrs, "0;;0;"),
    VALUE(CONCAT(digits))
  )
whereas to eliminate digits along with other non-letters, a different trick
Code: 
= LET(
    chrs, Explodeλ(string),
    isNum, EXACT(UPPER(chrs),LOWER(chrs)),
    CONCAT(FILTER(chrs, NOT(isNum)))
  )
Click to expand...
Hi Peter Bartholomew, You are correct, to extract only number the Text format code is "0;;0;"

So, here is my formula as in:

1] Extract only number:

=CONCAT(TEXT(MID(A1,SEQUENCE(LEN(A1),1),1),"0;;0;"))

2] Extract only text:

=CONCAT(TEXT(MID(A1,SEQUENCE(LEN(A1),1),1),";;"))

Deepak said:
Hi, I think you should to look at (7) Numeric data find out through excel | Chandoo.org Excel Forums - Become Awesome in Excel

This is for extracting number and solutions are great.

Very interesting solutions by Sajan, Lori, Narayan....
Click to expand...
Hi, Deepak, Thank you for your link.

The extract only number formula to use NVP+SEQUENCE function become:

=NPV(-0.9,,IFERROR(0+MID(A1,SEQUENCE(LEN(A1),1,LEN(A1),-1),1),""))%

Regards
Bosco
 
bosco_yip said:
Hi Peter Bartholomew, You are correct, to extract only number the Text format code is "0;;0;"

So, here is my formula as in:

1] Extract only number:

=CONCAT(TEXT(MID(A1,SEQUENCE(LEN(A1),1),1),"0;;0;"))

2] Extract only text:

=CONCAT(TEXT(MID(A1,SEQUENCE(LEN(A1),1),1),";;"))


Hi, Deepak, Thank you for your link.

The extract only number formula to use NVP+SEQUENCE function become:

=NPV(-0.9,,IFERROR(0+MID(A1,SEQUENCE(LEN(A1),1,LEN(A1),-1),1),""))%

Regards
Bosco
Click to expand...

Awesome bosco.
This SEQUENCE looks interesting, Well, I am not working with excel these days so less known to new functions.
 
Lara_1234 said:
I have a text in a1 = "ab50,bc60", i want result in b2 as "abbc"
Click to expand...
=LEFT(A1,FIND(",",A1)-1)&RIGHT(A1,FIND(",",A1, FIND(",",A1)+1)-FIND(",",A1)-1)

=SUBSTITUTE(SUBSTITUTE(A1,LEFT(A1,FIND(",",A1)-1),"",1),RIGHT(A1,LEN(A1)-FIND(",",A1)),"",1)
 
Status
Not open for further replies.
Back
Top