Extract only text

[Lara_1234]

I have a text in a1 = "ab50,bc60", i want result in b2 as "abbc"

 

[bosco_yip]

B1:

=SUBSTITUTE(CONCAT(TEXT(MID(A1,ROW($1:$99),1),";;")),",",)

or,

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

Array entry depends on your version of Excel.

Regards

 

[Peter Bartholomew]

@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)

= LET(
    chrs, Explodeλ(string),
    digits, TEXT(chrs, "0;;0;"),
    VALUE(CONCAT(digits))
  )

whereas to eliminate digits along with other non-letters, a different trick

= LET(
    chrs, Explodeλ(string),
    isNum, EXACT(UPPER(chrs),LOWER(chrs)),
    CONCAT(FILTER(chrs, NOT(isNum)))
  )

 

[Deepak]

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....

 

[bosco_yip]

@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)

= LET(
    chrs, Explodeλ(string),
    digits, TEXT(chrs, "0;;0;"),
    VALUE(CONCAT(digits))
  )

whereas to eliminate digits along with other non-letters, a different trick

= LET(
    chrs, Explodeλ(string),
    isNum, EXACT(UPPER(chrs),LOWER(chrs)),
    CONCAT(FILTER(chrs, NOT(isNum)))
  )

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, 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....

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

 

[Peter Bartholomew]

@bosco_yip
Lori was never short of innovation was he!
These days I am more likely to consult him on functional programming, though.

 

[bosco_yip]

@Peter Bartholomew

Agree, and we all learn in each other.

 

[Deepak]

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

Awesome bosco.
This SEQUENCE looks interesting, Well, I am not working with excel these days so less known to new functions.

 

[Pasadu]

I have a text in a1 = "ab50,bc60", i want result in b2 as "abbc"

=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)

 

[bosco_yip]

=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)

Open a new thread for your new question,

This thread is closed.