How can I extract the digital part, e.g. "23" in "s 23 secs." in all the column cells

**DXMM2007** · 11-01-2007, 09:59 AM

Dear all,

There are some data in my column, like
"s 23 secs.
s 20 secs.
s 26 secs.
s 12 secs.
s 29 secs.
s 11 secs.
s 38 secs.
s 17 secs.
s 27 secs.
s 17 secs.
"

How can I extract the digital part only from this column, e.g. make "s 23 secs." as "23", whilst the rest are with the same principle.

Thanks in advance,

Regards,

DXMM2007

**oldchippy** · 11-01-2007, 10:05 AM

Try this,

=
MID(A1,3,2)*1 by multiplying by 1, you can then use the number in calculations

**DXMM2007** · 11-01-2007, 10:21 AM

Hey oldchippy,

Thanks for your reply.

It works successfully for the single cell in the column. However, I wanna do it for each item in the column. Say there are 500 items need to convert in the column, how can I do it automatically?

Originally Posted by oldchippy

Try this,

=
MID(A1,3,2)*1 by multiplying by 1, you can then use the number in calculations

**oldchippy** · 11-01-2007, 11:17 AM

Hi, just auto-fill down the column

**DXMM2007** · 11-01-2007, 11:54 AM

Originally Posted by oldchippy

Hi, just auto-fill down the column

Hey oldchippy,

Thank you so much for the pics and suggestions!

I realize how to do it!

Regards,

DXMM2007

**oldchippy** · 11-01-2007, 11:56 AM

Glad to help - thanks for the feedback

**Ron Coderre** · 11-01-2007, 10:14 AM

Just in case the location of the numbers is not consistent....

This formula returns consecutive numbers from anywhere in the text:
=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000))))

Does that help?