MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Extracting numbers from a long number.


Posted by Ernesto on April 23, 2001 8:29 PM

I have cells with numbers:

-A-

A3010251565165
A30112055151587
A301019592
A301093516

What I need is a quick way to get the two numbers that
come after A301. The rest of the numbers can be
discarded.
Thanks for any help,
Ernesto
ps, Aladin, you are my excel idol!


Posted by Steve W on April 23, 2001 10:10 PM

Hi ernesto
try this
c1 is the cell with your number.
=RIGHT(LEFT(C1,6),2)
steve

Posted by David Hawley on April 23, 2001 10:19 PM

Hi Ernesto

There are a few ways this can be done.

1. Select your numbers then go to Data>Text to columns.

2. Select "Fixed Width" and click Next.

3. Click where you want your "Line Break" ie; after "A301" Click Next.

4. Select you destination and Format and click finish. That's it!

...or in B2 put this formula

=VALUE(SUBSTITUTE(A1,"A301",""))

Copy down as far as needed then copy and Pastespecial as Values over the top.


Dave

OzGrid Business Applications

Posted by Aladin Akyurek on April 24, 2001 12:00 AM

If Steve's interpretation is right on track, the following is an alternative to his formula:

=VALUE(MID(A1,5,2))

VALUE converts the result of MID into a number.

The option Data|Text to Columns can also be used with the interpretation in question.

Aladin

Posted by Ernesto on April 24, 2001 3:53 PM

Thanks guys!
I did it by using the columns to text, and
specifying the column widths. I can't believe
I didn't even think of that!
But thanks so much!
This is such a great forum!

Ernesto