Forum Discussion

krkarthik_info's avatar
krkarthik_info
Frequent Contributor
9 years ago

How to compare the cell value in excel file?

Hi All,

 

I am having two excel files with column Formula. Columns having the data like below

 

Formula in Excel 1Formula in Excel 2
C₂₁H₂₁O₆C21H21O6
C₂₂H₂₂O₂F³²SC22H22O2F[32]S

 

On comparing the cell values[C₂₁H₂₁O₆ and C21H21O6], it shows they both are not matched.

 

Actually they both are same formula.

 

How to handle this scenario?

 

Can anyone post your valuable suggestion?

 

Thanks in advance.

 

Thanks,

Karthik K R

  • krkarthik_info's avatar
    krkarthik_info
    9 years ago

    Hi All, 

     

    I have come with a workaround to compare the value character by character, by getting its Unicode character. Figured out the below Unicode characters for digits, subscript digits and superscript digits as below:

     

    For Digits[1-9]:

    1 – 49, 2 – 50, 3 – 51, 4 – 52, 5 – 53, 6 – 54, 7 – 55, 8 – 56, 9 – 57

     

    For Subscript Digits[1-9]:

    1 – 8321, 2 – 8322, 3 – 8323, 4 – 8324, 5 – 8325, 6 – 8326, 7 – 8327, 8 – 8328, 9 – 8329

     

    For Superscript Digits[1-9]:

    1 – 185, 2 – 178, 3 – 179, 4 – 8308, 5 – 8309, 6 – 8310, 7 – 8311, 8 – 8312, 9 – 8313

     

    Thanks all for the suggestions.

     

    Thanks,

    Karthik K R

  • Colin_McCrae's avatar
    Colin_McCrae
    Community Hero
    9 years ago

    Well, they're not the same. The formatting/notations used are obviously different.

     

    How are you comparing them? In TestComplete? Having read them in from Excel? Using what? There are quite a few comparison options in TestComplete. Need a little more detail.

     

    Your second one clearly won't match either due to the brackets round the 32 (as well as the other stuff). By the looks of it, you'll need to parse them to standardise the notation used. I imagine there are other notations you'll need to account for as well.

     

    Not sure you'll get a simple, one size fits all, answer for this one. Might be able to do something clever with RegEx but complex RegEx is not my speciality I'm afraid .....

    • krkarthik_info's avatar
      krkarthik_info
      Frequent Contributor
      9 years ago

      Hi Colin,

       

      Thanks for the response.

       

      I am using TestComplete with Excel COM object "Excel.Application" to compare the cell values whether they are equal or not.

       

      Even I am not experienced more into RegEx :smileyhappy:

       

      Thanks,

      Karthik K R

      • Colin_McCrae's avatar
        Colin_McCrae
        Community Hero
        9 years ago

        So you're reading in the value and then comparing them? So probably as strings? (I suspect that depends how the cells in Excel are formatted)

         

        In which case, my original answer stands, you're going to have to do some standardisation of the notation if you want a simple string compare to work. A bunch of "Replace" type statements would work, but it feels like there should be a more efficient way? But nothing is immediately coming to mind ....

  • dbattaglia's avatar
    dbattaglia
    Contributor
    9 years ago

    krkarthik_info looks like you need to choose a standard format and convert both values to that format for the comparison, like Colin said.

     

    I would stay away from regex unless you really need it. You should be able to accomplish this using plain string functions.

     

    Let us know how it goes.

     

    Daniel

  • krkarthik_info's avatar
    krkarthik_info
    Frequent Contributor
    9 years ago

    Hi All, 

     

    I have come with a workaround to compare the value character by character, by getting its Unicode character. Figured out the below Unicode characters for digits, subscript digits and superscript digits as below:

     

    For Digits[1-9]:

    1 – 49, 2 – 50, 3 – 51, 4 – 52, 5 – 53, 6 – 54, 7 – 55, 8 – 56, 9 – 57

     

    For Subscript Digits[1-9]:

    1 – 8321, 2 – 8322, 3 – 8323, 4 – 8324, 5 – 8325, 6 – 8326, 7 – 8327, 8 – 8328, 9 – 8329

     

    For Superscript Digits[1-9]:

    1 – 185, 2 – 178, 3 – 179, 4 – 8308, 5 – 8309, 6 – 8310, 7 – 8311, 8 – 8312, 9 – 8313

     

    Thanks all for the suggestions.

     

    Thanks,

    Karthik K R

    • Colin_McCrae's avatar
      Colin_McCrae
      Community Hero
      9 years ago

      I like how you mark your own post as the solution.

       

      Various people suggest you need to normalise the formatting. And that's what you've done. You have done as the replies (not just mine, several others) suggested, and then marked your own post as the solution! :smileyvery-happy: