Text indicator when pasting values

Pam Bates
Pam Bates
edited 12/09/19 in Formulas and Functions

I have a 10 digit "Store #".  We use the right 5 digits as a "Site ID".  I did a "Right" function to grab the 5 right digits.  My Index/Match formula returned a #No Match error. So I copied & pasted values to get rid of the formula. Index/Match still did not work.  I found a hidden leading apostrophe (like Excel when you want a number or formula to be text).   If I manually remove the apostrophe the Index/Match works.  How can I get rid of the leading apostrophes?  I have a couple thousand rows......

  • I have tried to do a (Ctl-F) find/replace but it does not seem to recognize the apostrophe
  • I thought maybe since I was trying to do this in the Primary column..... but it did the same thing in another column.
  • Keeping any leading zeros would be nice but not required.
  • The sheet does have Parent/Child rows so not sure if that complicates things.
  • The other sheet in the Index/Match is not mine so not sure if I can get the owner to adjust their sheet.  
  • Is there a better way to parse the digits I need?




  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Always compare like-to-like

    RIGHT(some-text, 2) returns Text

    You need to compare to Text, not Number, or convert to Number first.

    Somewhere on same row:

    =RIGHT([Store #]@row,5)

    Somewhere else, look at the whole column with the RIGHT formula

    =MATCH("00230", [that-column]:[that-column], 0)

    should find it.

    There are cases where the number would be easier to setup, so...

    =VALUE(RIGHT([Store #]@row,5))

    =MATCH(230, [that-column]:[that-column], 0)

    should also find it.

    I hope that helps.

    re: "The sheet does have Parent/Child rows so not sure if that complicates things." - it doesn't.


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I usually go with the VALUE route when dealing with numbers, but then again... When I am dealing with numbers I am usually doing some kind of math, so VALUE is necessary.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!