Results 1 to 3 of 3

Thread: Match with Trim

  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Is it possible to use TRIM in a MATCH formula?

    I have a name (1) which I would like to match in a column of names where each name has bank spaces appended to the name. The list of names with the blank spaces is a data extract from another system. I know I can write some VBA code to TRIM each cell; not a preferred method. Can it be done in a formula?


    (1) Excludes blank spaces appended to the name.


    A sample workbook is attached.

    Thanks,
    John
    Attached Files Attached Files

  2. #2
    Lounger
    Join Date
    Mar 2009
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This seems to work with your sample data, copying a name into I3, and without the spaces

    =MATCH(I3,TRIM(C5:C10),0) entered as an array formula.
    Attached Files Attached Files

  3. #3
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post
    ..............
    The list of names with the blank spaces is a data extract from another system. I know I can write some VBA code to TRIM each cell; not a preferred method. Can it be done in a formula?
    1] Your formula in I8 :

    =MATCH(I4,C:C,0)

    2] Try to use this revised formula instead :

    =MATCH(I4&"*",C:C,0)


    Regards
    Bosco

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •