How to convert 1000maniacs to 1000

[ianccy]

i want ot enter a formula in cell B2 so that the data in Cell A2 "1000maniacs" becomes "1000", this could be easy, but... thank you
This message was edited by ianccy on 2002-08-30 04:36

 

[Sir Vili]

Hello ianccy.

Here is formula to get Value:

=VALUE(LEFT(A2,4))

Another formula is (there are many):

=VALUE(MID(SUBSTITUTE(A2," "," "),SEARCH("1???",SUBSTITUTE(A2," "," ")),4))

BRGDS Sir Vili.
This message was edited by Sir Vili on 2002-08-30 04:49

 

[ianccy]

Thank you

 

[Aladin Akyurek]

On 2002-08-30 04:34, ianccy wrote:
i want ot enter a formula in cell B2 so that the data in Cell A2 "1000maniacs" becomes "1000", this could be easy, but... thank you
This message was edited by ianccy on 2002-08-30 04:36

Are the entries in A always a fixed number of digits immediately followed by a number of letters?

 

[ianccy]

Sometime it's only 3 digit of no. then follow by some Letters. By the way, if i have "555.85ABC" how to convert to "555.85"?

 

[Aladin Akyurek]

On 2002-08-30 06:18, ianccy wrote:
Sometime it's only 3 digit of no. then follow by some Letters. By the way, if i have "555.85ABC" how to convert to "555.85"?

In B2 enter:

=SUBSTITUTE(A2,SUBSTITUTE(A2,LEFT(A2,SUMPRODUCT((LEN(A2)-LEN(SUBSTITUTE(A2,{".",0,1,2,3,4,5,6,7,8,9},""))))),""),"")+0

where A2 houses an entry like 555.85ABC or 1000maniacs.

 

[Sir Vili]

Hello Aladin.

I am every time surprised Your ability to build formulas. Some day I need again Your help.

I figured out one formula more, although I do not understand it correctly, it seems to work. Here it is as array entered:

=1*LEFT(A2,MATCH(FALSE,ISERROR(1*LEFT(A2,ROW($1:$65536))),9.99999999999999E+307))

What do think?

Best regards Sir Vili.

 

[Aladin Akyurek]

On 2002-09-01 03:57, Sir Vili wrote:
Hello Aladin.

I am every time surprised Your ability to build formulas. Some day I need again Your help.

I figured out one formula more, although I do not understand it correctly, it seems to work. Here it is as array entered:

=1*LEFT(A2,MATCH(FALSE,ISERROR(1*LEFT(A2,ROW($1:$65536))),9.99999999999999E+307))

What do think?

Best regards Sir Vili.

Great. It will also work on entries like 12TX8 (if the 12 bit is what must be extracted). I propose the following amendements (some cosmetic, some substantial):

=LEFT(A1,MATCH(FALSE,ISERROR(0+LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))))))+0

If the morefunc add-in available, then:

=LEFT(A1,MATCH(FALSE,ISERROR(0+LEFT(A1,INTVECTOR(LEN(A1),1,0,1)))))+0

Both array-entered.