locked
How to extract excel cell address from formula RRS feed

  • Question

  •             var myString1 = "=D6/D2";
                var myString2 = "=A6/Z2-1";
                var myString3 = "=(X6/B2)-5";
                var onlyLetters1 = myString1.Where(Char.IsLetterOrDigit).ToList();
                var onlyLetters2 = myString2.Where(Char.IsLetterOrDigit).ToList();
                var onlyLetters3 = myString3.Where(Char.IsLetterOrDigit).ToList();

    suppose above code does not work. see the above code there is cell address like D6 or D2 etc. now how can i extract cell address from formula if the formula is simple or complex.

    i want to store cell address in List<String>

    please guide me. thanks

    Monday, May 4, 2020 10:31 AM

Answers

  • Hi Sudip_inn,

    Thank you for posting here.

    Maybe you want to extract excel cells from the string?

    If so, you can use Regex to do this.

                string regex = @"\$?[A-Z]{1,3}\$?[0-9]{1,7}";
                MatchCollection matchCollection = Regex.Matches(myString3, regex);

    If you need them to appear in pairs, you can do it like this:

                string regex = @"\$?[A-Z]{1,3}\$?[1-9]{1,7}(/\$?[A-Z]{1,3}\$?[1-9]{1,7})?";
                var myString3 = "=(X6/B2)-5";
                MatchCollection matchCollection = Regex.Matches(myString3, regex);
    

    Best Regards,

    Timon


    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.

    • Marked as answer by Sudip_inn Wednesday, May 6, 2020 5:23 PM
    Tuesday, May 5, 2020 5:53 AM
  • Hi Sudip_inn,

    Try the first regex.

                string regex = @"\$?[A-Z]{1,3}\$?[0-9]{1,7}";
                var myString3 = "=(X6/B2)-5";
                MatchCollection matchCollection = Regex.Matches(myString3, regex);
                Console.WriteLine(matchCollection.Count);
                foreach (var item in matchCollection)
                {
                    Console.WriteLine(item);
                }

    Result:

    Best Regards,

    Timon


    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.

    • Marked as answer by Sudip_inn Wednesday, May 6, 2020 5:23 PM
    Tuesday, May 5, 2020 7:47 AM

All replies

  • See if this will work for you

    https://codingsense.wordpress.com/2009/03/01/get-all-formula-from-excel-cell/


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, May 4, 2020 10:53 AM
  • Hi Sudip_inn,

    Thank you for posting here.

    Maybe you want to extract excel cells from the string?

    If so, you can use Regex to do this.

                string regex = @"\$?[A-Z]{1,3}\$?[0-9]{1,7}";
                MatchCollection matchCollection = Regex.Matches(myString3, regex);

    If you need them to appear in pairs, you can do it like this:

                string regex = @"\$?[A-Z]{1,3}\$?[1-9]{1,7}(/\$?[A-Z]{1,3}\$?[1-9]{1,7})?";
                var myString3 = "=(X6/B2)-5";
                MatchCollection matchCollection = Regex.Matches(myString3, regex);
    

    Best Regards,

    Timon


    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.

    • Marked as answer by Sudip_inn Wednesday, May 6, 2020 5:23 PM
    Tuesday, May 5, 2020 5:53 AM
  • see this is not worked

                string regex1 = @"\$?[A-Z]{1,3}\$?[1-9]{1,7}(/\$?[A-Z]{1,3}\$?[1-9]{1,7})?";
                var myString4 = "=A6/Z2-1";
                MatchCollection matchCollection1 = Regex.Matches(myString4, regex1);

    matchcollection should have count 2 because there is two cell address A6 and Z2 but if you test above code which return count 1 and return result as A6/Z2

    please tell me what to change in code.

    Tuesday, May 5, 2020 7:05 AM
  • Hi Sudip_inn,

    Try the first regex.

                string regex = @"\$?[A-Z]{1,3}\$?[0-9]{1,7}";
                var myString3 = "=(X6/B2)-5";
                MatchCollection matchCollection = Regex.Matches(myString3, regex);
                Console.WriteLine(matchCollection.Count);
                foreach (var item in matchCollection)
                {
                    Console.WriteLine(item);
                }

    Result:

    Best Regards,

    Timon


    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.

    • Marked as answer by Sudip_inn Wednesday, May 6, 2020 5:23 PM
    Tuesday, May 5, 2020 7:47 AM