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
 10.5K Get Help
 61 Global Discussions
 46 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 54 Brandfolder
 124 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!