none
How to parse excel formula and pickup cell address RRS feed

  • Question

  • in my apps excel formula saved in db. which i need to change by c# code

    My formula looks like (AM453+AM468+AM483+AM498) which should be change like below one. 

    IF(AM453="","",IF(AM468="","",IF(AM483="","",IF(AM498="","",(AM453+AM468+AM483+AM498))))

    AM468 or AM453 etc are cell address in excel.

    My formula also may look like (AM453+AM468+(AM483/AM498)) OR (AM453+AM468-(AM483*AM498))

    actually my formula could be anything which i need to parse and inject IF cell address is blank then return blank this way

    IF(AM453="","",IF(AM468="","",IF(AM483="","",IF(AM498="","",(AM453+AM468+AM483+AM498))))

    please share some routine which i can use to pick cell address one by one from any kind of formula. after pickup cell address i need to enter this one in formula like IF(AM453="","",.......)

    please guide me how to achieve this with sample routine. if possible share a sample routine. thanks

    Monday, February 25, 2019 7:23 AM

Answers

  • You can use a Regular Expression like this:

    using System.Text.RegularExpressions; ...

    string test = "(AM453+AM468+(AM483/AM498))";
    Regex re = new Regex(@"[A-Z]+\d+"); MatchCollection matches = re.Matches(test); List<string> cellAddresses = new List<string>(); foreach (Match m in matches) { cellAddresses.Add(m.Value); }


    This will give you the list of all addresses in "cellAddresses". You can now loop through the list and concatenate them adding your IFs.



    Monday, February 25, 2019 7:41 AM
    Moderator
  • Thanks for your code i have done this way. it solved my purpose.

                string test = "AQ443/AQ4/4";
                Regex re = new Regex(@"[A-Z]+\d+");
                MatchCollection matches = re.Matches(test);
                List<string> cellAddresses = new List<string>();
                foreach (Match m in matches)
                {
                    cellAddresses.Add(m.Value);
                    string celladd = m.Value;
                    string crosscalcformulatmp = "IF(" + celladd + "=\"\",\"\"," + celladd + ")";
                    test = ReplaceWholeWord(test,celladd, crosscalcformulatmp);
                }
            public string ReplaceWholeWord(string original, string wordToFind, string replacement, RegexOptions regexOptions = RegexOptions.None)
            {
                string pattern = String.Format(@"\b{0}\b", wordToFind);
                string ret = Regex.Replace(original, pattern, replacement, regexOptions);
                return ret;
            }


    • Marked as answer by Sudip_inn Friday, March 22, 2019 4:39 PM
    Wednesday, March 20, 2019 12:51 PM

All replies

  • You can use a Regular Expression like this:

    using System.Text.RegularExpressions; ...

    string test = "(AM453+AM468+(AM483/AM498))";
    Regex re = new Regex(@"[A-Z]+\d+"); MatchCollection matches = re.Matches(test); List<string> cellAddresses = new List<string>(); foreach (Match m in matches) { cellAddresses.Add(m.Value); }


    This will give you the list of all addresses in "cellAddresses". You can now loop through the list and concatenate them adding your IFs.



    Monday, February 25, 2019 7:41 AM
    Moderator
  • Not an answer to the actual question asked (I like Alberto Poblacion's<abbr class="affil"></abbr> answer), but if this will be used with Excel 365 I suggest you look at the new =IFS() in Excel function. It is designed for situations like this and is cleaner and easier than nested =IF()

    Ethan


    Ethan Strauss

    Monday, February 25, 2019 5:12 PM
  • Hi Sudip_inn,

    Thank you for posting here.

    I want to confirm if you used Excel interop(Microsoft.Office.Interop.Excel dll).

    We are waiting for your update.

    Best regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, February 26, 2019 9:38 AM
    Moderator
  • Thanks for your code i have done this way. it solved my purpose.

                string test = "AQ443/AQ4/4";
                Regex re = new Regex(@"[A-Z]+\d+");
                MatchCollection matches = re.Matches(test);
                List<string> cellAddresses = new List<string>();
                foreach (Match m in matches)
                {
                    cellAddresses.Add(m.Value);
                    string celladd = m.Value;
                    string crosscalcformulatmp = "IF(" + celladd + "=\"\",\"\"," + celladd + ")";
                    test = ReplaceWholeWord(test,celladd, crosscalcformulatmp);
                }
            public string ReplaceWholeWord(string original, string wordToFind, string replacement, RegexOptions regexOptions = RegexOptions.None)
            {
                string pattern = String.Format(@"\b{0}\b", wordToFind);
                string ret = Regex.Replace(original, pattern, replacement, regexOptions);
                return ret;
            }


    • Marked as answer by Sudip_inn Friday, March 22, 2019 4:39 PM
    Wednesday, March 20, 2019 12:51 PM