+ Reply to Thread
Results 1 to 5 of 5

Help extracting certain portion of a cell

  1. #1
    Registered User
    Join Date
    03-11-2013
    Location
    Cape Town
    MS-Off Ver
    Excel 2010
    Posts
    2

    Help extracting certain portion of a cell

    Hi,

    Hoping someone can help a newbie here... I have attached a file we need to format and instructions below of what is required. I don't think it's complex, just need to know the right formulas and approach to use.

    What we need:

    1. Each evening we export the card payments for that day from our database into an Excel spread sheet. The format of the data exported can be seen in the "Transactions" worksheet of the attached document.

    2. At the moment we physically type each transaction, one by one, into the system we use. What we want to do is simply upload a CSV batch file onto our card processors website. In order to do this our data needs to be in a format requested by the card processor. This format is shown in the "Batch" worksheet of the attached document.

    3. I have added comments to explain the "changes" that need to be made to the data contained in the Transactions" worksheet when moved to the corresponding cells in the "Batch" worksheet.

    4. Some data is semi-permanent and pulled from the "Field Settings" worksheet.

    5. So what we require is a small program/formulasor macro that will populate the "Batch" worksheet cells in the correct format based on what data we have placed into the "Transactions" worksheet. We will then save the "Batch" worksheet as a CSV file and upload it to the card processors website.

    Please would some excel wizard be able to help?

    Thanks
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor Harribone's Avatar
    Join Date
    02-24-2013
    Location
    Midlands, UK
    MS-Off Ver
    Excel 2013/2016/365
    Posts
    528

    Re: Help extracting certain portion of a cell

    PendingTransactions Template.xlsx

    Please see attached file which does most of this. You just need to select the whole row and drag down to copy formulas as far as required (so each entry is shown).
    I struggled to get the ddmm custon format work for some reason it just wasn't playing ball. This is only thing that needs sorting.

    Note that if you are using to create single sheets of data to save, copy the entire sheet (click square left of A and above 1 in left corner), copy this (Right click > copy) then paste it back on itself or a new sheet (right click> paste special>values). This will keep the data intact but remove formulas.

    EDIT: This assumes your Transactions sheet always starts with data on row 7. Ignore cell D11 on the Transactions sheet, that was me messing about and it can be deleted.
    Say thanks, click *

  3. #3
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Help extracting certain portion of a cell

    Hi -

    Try the attached.

    Hope that helps.
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Help extracting certain portion of a cell

    Looks like Harribone and I had the same ideas. I used RIGHT and LEFT string functions to extract the date to a four digit format so you can go ahead and use that.

    Good luck.

  5. #5
    Registered User
    Join Date
    03-11-2013
    Location
    Cape Town
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Help extracting certain portion of a cell

    Thank you to both you chaps. Works perfectly! You have just saved me hours of work :-)
    Really appreciate it.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1