Too Much Data Per Cell, Search/copy/paste Specific Values?

    Hi. From mySQL database, i am getting a ton of data that is all being inputted into a single excel cell. Within this cell, there are certain data points that i wish to obtain (Bank #, SSN #, Routing #). There are multiple difficulties in obtaining this information.


    1. these cells contain the same fields, but different number of total characters (differing in names, addresses, etc.)


    2. some of the data points (like SSN) are inputted incorrectly, so even though a SSN is only 9 digits/characters, I may have to output all 11 that the SQL database placed within the cell.


    Can I get some help? I'm thinking of a search function/macro within excel, because all of the fields show up correctly.


    something like (i know this doesn't work)


    = or("cellA1" contains/finds/function SSN,return the 9 characters after "SSN ")


    um...yeah. difficult to explain, i'm sorry!



    edit: not sure what is wrong the file. I have attached a new one. I really think this should be possible. Thanks!

    Re: Too Much Data Per Cell, Search/copy/paste Specific Values?


    effang,


    Welcome aboard. Your file is corupted or something and I can't get it to open. The extension is "xlsx". Can you post the file again. I think the solution will be pretty straightforward if the data has a predictable format.


    Jim

    Re: Too Much Data Per Cell, Search/copy/paste Specific Values?


    no, the format changes entry to entry, due to the fact that the number of chars is always changing.


    an applicant's name, address, will all add different number of chars to the single cell entry. i tried to exhibit that in the example...if it is not clear, please! let me know! thanks.

    Re: Too Much Data Per Cell, Search/copy/paste Specific Values?


    effang,


    I thought I did let you know it wasn't clear. I can't open the file. I can't help until I see the data layout. Unless, have you tried [Data][Text To Columns...] from the Excel menus? Otherwise, repost the file.


    Jim

    Re: Too Much Data Per Cell, Search/copy/paste Specific Values?


    sorry! English is not my native language. There was a slight mix up. My 2nd post was after I uploaded the new .xls file. It is now in the 97-2003 excel format and should be viewable by most users!

    Re: Too Much Data Per Cell, Search/copy/paste Specific Values?


    Effang, welcome to the forum -- you will find it's a great asset when used properly.


    We have a hard and fast rule about cross-posting: you must include a link to the threads on any other sites where you have posted the same question.


    MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

    *


    Then I or one of the other moderators will unlock this thread.

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

    Re: Too Much Data Per Cell, Search/copy/paste Specific Values?


    In C5 and copy down (to strip quotes, spaces, and hyphens):


    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5,"""", ""), " ", ""), "-", "")


    Then,
    E3: 10 (length of bank account)
    F3: 9 (length of SSN)
    G3: 9 (length of routing number)


    E4: bank_account_number:
    F4: ssn:
    G4: bank_routing_number:


    E5 and copied right and down: =MID($C5, FIND(E$4, $C5) + LEN(E$4), E$3)

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login