Text indicator when pasting values

Pam Bates

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

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.

Craig

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.