Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Removing Text from numbers

0 views
Skip to first unread message

Michael

unread,
Jul 24, 2003, 11:41:02 AM7/24/03
to
Help!!! Is there a way in a string numbers ending with
text to seperate the numbers from the text. Sample data
24594A 24594B 12345MIK 123AB The numder of numbers and
text are variable.

Thanks
Michael

GerryK

unread,
Jul 24, 2003, 12:03:19 PM7/24/03
to
Try adapting this, array entered

=LEFT(A1,MIN(IF(1-ISERROR(FIND({1,2,3,4,5,6,7,8,9},A1)),
FIND({1,2,3,4,5,6,7,8,9},A1)))-1)&
RIGHT(A1,LEN(A1)-MAX(IF(1-ISERROR(FIND
({1,2,3,4,5,6,7,8,9},A1)),
FIND({1,2,3,4,5,6,7,8,9},A1))))

Gerry

>.
>

Aladin Akyurek

unread,
Jul 24, 2003, 12:20:13 PM7/24/03
to
=SUBSTITUTE(A1,SUBSTITUTE(A1,LEFT(A1,SUMPRODUCT((LEN(A1)-LEN(SUBSTITUTE(A1,{
0,1,2,3,4,5,6,7,8,9},""))))),""),"")+0

which expects a series of digits followed by a series of non-digits.

"Michael" <kni...@lineone.net> wrote in message
news:00d301c351f9$fcd3c770$7d02...@phx.gbl...

Ron Rosenfeld

unread,
Jul 24, 2003, 2:40:29 PM7/24/03
to

Assuming the numbers come first and then come the letters, the following
*array-entered* formulas will do that. The string is assumed to be in cell A4.

For the numbers:

=LEFT(A4,MATCH(TRUE,ISERROR(-MID(A4,ROW(INDIRECT("1:"&LEN(A4))),1)),0)-1)

For the letters:

=MID(A4,MATCH(TRUE,ISERROR(-MID(A4,ROW(INDIRECT("1:"&LEN(A4))),1)),0),255)

To *array-enter* a formula, hold down <ctrl><shift> while hitting <enter>. XL
will place braces {...} around the formula.


--ron

0 new messages