Extract text from a custom formatted cell

{ 34 Comments }

By Ashish Mathur · · · Leave a comment

In range G5:G44, some numbers are entered.  These numbers are custom formatted with General” Dr” for debit entries and General” Cr” for credit entries.

One may want to extract the Dr and Cr in range H5:H44.

The following process will get the desired result:

1. Press Ctrl+F3 > New and in the Name box, type cell_format
2. In the Refers to box, enter this formula and click on Close

=GET.CELL(53,data!$G5)

3. In cell H5, enter this formula and copy down till cell H44

=RIGHT(cell_format,2)

Dr and Cr should now get appear in range H5:H44.

Leave a Comment

Your email address will not be published.

CAPTCHA

*

    • Hi

      Here’s a simple solution to your problem. My end goal is to convert all credits to negative numbers:

      1. Open a new blank excel workbook and “Save as” -> .csv format (Comma delimited). A .csv file does not store formulae, formatting etc.

      2. From your Tally-extracted excel file, copy the sheet with the Dr-Cr formatted columns and paste into the blank .csv file

      3. Save and close the .csv file. Now reopen it.

      4. You’ll see that all the columns in the .csv file with Dr-Cr formats now include Dr and Cr suffixed as a part of the string itself. This is now easy to work with as it can be easily read by any normal excel formula. Formulae otherwise don’t read Cr/Dr when they’re part of the format itself.

      5. Copy all the data back from your .csv into a new sheet (say, Sheet 2) in your excel.

      6. In sheet 3 of the excel file, apply the following formula for the cells with data (here I’m using cell H5 as an example):
      =IF(RIGHT(‘Sheet 2’!H5,2)=”Cr”,-‘Sheet 1′!H5,’Sheet 1’!H5)

      What does this do? From the Sheet 2 that contains your un-formatted data (with Dr/Cr along with the number as string), you are first reading whether that particular cell has “Cr” as the last 2 characters of the string. If it does, you are converting that cell to negative by specifying minus of the corresponding cell from Sheet 1. If there is no “Cr” present in the string, it simply returns the original value as it is from Sheet 1.

      This is a pretty simple solution as it gives you the flexibility to do whatever you want with your data once you can see Dr/Cr in the cell value.

      Reply

  • Hi Ashish,

    While reworking on the solutions I am having some hard time to solve these. Could you please help me with the quick solutions you gave us last time.
    1)Part II – Number appears as text: Calculate the net profit
    Particulars
    Revenue 100 cr
    Operating costs 20 dr
    Interest income 12 cr
    Interest costs 3 dr

    2) Part II – Data from mail/web
    Number
    123
    34
    56789
    (For this problem, I have tried all possible ways to change the text to number (paste special option, changing the format to no. etc) but its not working)

    I would really appreciate if you could help me on these.

    Reply

    • Hi,

      One simple way to solve the first problem is to Copy the data into MS Word and replace cr i.e. space and cr with nothing (click on Replace All). Then replace dr i.e. space and dr with -. Copy this data back into MS Excel and go to Data > Text to columns > Finish.

      For the second question, copy this data to MS Word and find for space and click on Replace All. Copy this data back to MS Excel.

      Hope this helps.

      Reply

  • Hi Ashish,

    This was really helpful, Just out of curiosity for the first problem Can’t we use “substitute” function in excel itself and for the second one “if it was the space which was causing the problem” can’t we use “TRIM” function. Many thanks in advance.

    Reply

  • Great solution. It works.
    is 53, shortcut for “format” value of cell Formula
    Where else can we use Get.cell. I have never seen such a use of the cell.

    Reply

    • Thank you. Yes, 53 is the code for format of the cell. Get.cell can only be used in named ranges. This was a part XLM 4.0 Macros which pre date VBA. Once VBA came mainstream, these XLM 4.0 Macros could be used only in named ranges.

      Reply