Grupos de Google ya no admite nuevas publicaciones ni suscripciones de Usenet. El contenido anterior sigue siendo visible.
Dismiss

Removing Text from numbers

Visto 0 veces
Saltar al primer mensaje no leído

Michael

no leída,
24 jul 2003, 11:41:0224/7/03
a
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

no leída,
24 jul 2003, 12:03:1924/7/03
a
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

no leída,
24 jul 2003, 12:20:1324/7/03
a
=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

no leída,
24 jul 2003, 14:40:2924/7/03
a

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 mensajes nuevos