# How to convert 1000maniacs to 1000

#### ianccy

##### Active Member
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

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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

Thank you

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?

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"?

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.

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.

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

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.

Replies
3
Views
265
Replies
4
Views
297
Replies
3
Views
221
Replies
1
Views
630
Replies
1
Views
96

1,207,260
Messages
6,077,351
Members
446,279
Latest member
hoangquan2310

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back