Extracting numbers from a String (Need to try this again).

[Zac]

Initially in AJ25 I had 123456789,
Now I have 1234567.89

I want to extract the numbers as follows:
Cell P25 should end up with the 1.
Cell Q25 should end up with the 2,
Cell R25 should end up with the 3,
etc. all the way thru, including cell X.
I don't want the "decimal point" to extract.

I've tried the following:

=MID($AJ$25,Column()-1,1)
on the number 123456789, and copied this from P thru X,
but all I got were blank cells from P thru X.

Any suggestions?

Thanks,
Zac

 

[Zac]

Let's forget the decimal point for now.

Using 123456789, why doesn't the MID formula work?

 

[Tim Francis-Wright]

To fix the decimal point, have AK25 (or some other cell) = SUBSTITUTE(AJ25,".","")

The cells in P through X are failing you because COLUMN() equals 16 to 25 on that
range: Excel is looking for the 15th through 24th byte in your 10-byte string.

I would replace COLUMN()-1 with COLUMN()-COLUMN($O$1)
That should do the trick.

 

[Mark W.]

On 2002-03-20 10:30, Zac wrote:
Initially in AJ25 I had 123456789,
Now I have 1234567.89

I want to extract the numbers as follows:
Cell P25 should end up with the 1.
Cell Q25 should end up with the 2,
Cell R25 should end up with the 3,
etc. all the way thru, including cell X.
I don't want the "decimal point" to extract.

I've tried the following:

=MID($AJ$25,Column()-1,1)
on the number 123456789, and copied this from P thru X,
but all I got were blank cells from P thru X.

Any suggestions?

Thanks,
Zac

After selecting cells P25:X25 enter the following array formula...

{=MID(SUBSTITUTE(AJ25,".",""),COLUMN(INDIRECT("1:"&LEN(AJ25)-1)),1)}

Array formulas are entered using the Control+Shift+Enter key combination. For info on array formulas see the Excel Help topic for "About array formulas and how to enter them".

 

[Aladin Akyurek]

On 2002-03-20 10:30, Zac wrote:
Initially in AJ25 I had 123456789,
Now I have 1234567.89

I want to extract the numbers as follows:
Cell P25 should end up with the 1.
Cell Q25 should end up with the 2,
Cell R25 should end up with the 3,
etc. all the way thru, including cell X.
I don't want the "decimal point" to extract.

I've tried the following:

=MID($AJ$25,Column()-1,1)
on the number 123456789, and copied this from P thru X,
but all I got were blank cells from P thru X.

Any suggestions?

Thanks,
Zac

I noticed no one is attempted to tell how you can adapt

=MID($AJ$25,Column()-1,1)

to your specs you clearly stated:

In P25 enter and copy across as far as needed:

=MID($AJ$25*100,COLUMN()-15,1)

15 is the number of columns that precedes column P.

COLUMN()-15 in P25 will return 1 -- exactly the number we need the formula to evaluate in P25:

=MID($AJ$25*100,16-15,1)

in Q25 to

=MID($AJ$25*100,17-15,1)

etc.

Note that the number in AJ25 is multiplied by 100 to make it a whole number.

Aladin