Extracting numbers from a String (Need to try this again).
MZ Tools makes life easier for the Excel VBA coder
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

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

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Mike
    Posts
    796
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Mike
    Posts
    796
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Let's forget the decimal point for now.

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

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    "Interfere? Of course we should interfere! Always do what you're best at, that's what I say!" -- The Doctor, Nightmare of Eden

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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".

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,861
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

      
    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


User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com