Text indicator when pasting values
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 (CtlF) 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?
Comments

Always compare liketolike
RIGHT(sometext, 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", [thatcolumn]:[thatcolumn], 0)
should find it.
There are cases where the number would be easier to setup, so...
=VALUE(RIGHT([Store #]@row,5))
=MATCH(230, [thatcolumn]:[thatcolumn], 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.
Craig

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
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.4K Get Help
 364 Global Discussions
 202 Industry Talk
 430 Announcements
 4.4K Ideas & Feature Requests
 137 Brandfolder
 129 Just for fun
 128 Community Job Board
 446 Show & Tell
 28 Member Spotlight
 1 SmartStories
 284 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!