How to compare the cell value in excel file?

[krkarthik_info]

Hi All,

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

Formula in Excel 1	Formula in Excel 2
C₂₁H₂₁O₆	C21H21O6
C₂₂H₂₂O₂F³²S	C22H22O2F[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

[Colin_McCrae]

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]

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 

Thanks,

Karthik K R

[Colin_McCrae]

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

[krkarthik_info]

Yes. You are right Colin.

I am also thinking for a different solution to solve this.

Thanks,

Karthik K R

[dbattaglia]

@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 · Accepted solution]

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]

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!