Having issues with =RIGHT formula not actually doing anything.

[justinwacker]

I have a simple spreadsheet with UPCs along with other information. I need to remove the first 2 digits from each UPC entry as they are too long. I am trying to use the =RIGHT formula to just grab the whole text string minus the two beginning numbers. What I'm getting is my formula in a cell with no result, as seen in the example below. If I click on my formula, in C2, it looks like this: =RIGHT(B2,LEN(B2)-2). In my experience this means the formula isn't written correctly but I've googled multiple times and I feel I'm entering it correctly. I'm hoping someone here can steer me into what I need to be doing.

My UPCs are entered as text, as if they get switched to numbers Excel removes ALL the preceding 0s and some of them are necessary. Tried using text to column and that's what happened. Coverted to numbers and I lost some essential 0s.

WHAT I GET:

14.97	00883929131716	=RIGHT(B2,LEN(B2)-2)
29.98	00883929024292
9.99	00097360301243

WHAT I WANT:

14.97	00883929131716	883929131716
29.98	00883929024292
9.99	00097360301243

[loginjmor]

I think your formula is correct. When you enter edit mode (click on the formula) Excel highlights and color codes paretheses pairs to help with debugging your formula. To keep Excel from treating the UPC's as numbers, format the cells as text and see if that helps.

[Tony Valko]

If you're seeing the actual formula in the cell that usually means the cell is formatted as TEXT.

Format the cell as General then press function key F2 then press Enter.

[justinwacker]

loginjmor, thanks, the UPCs were already formatted as text.

Tony Valko - Solved - That was it. My FORMULA column was somehow formatted as text as well, in a new sheet. Weird but now its something I know to look out for in the future.

Thank you!

[Tony Valko]

You're welcome. Thanks for the feedback!

[justinwacker]

Any advice on how I could have more easily put what is now Column C (results) where Column B (original numbers) was? If I tried to copy and paste Column C it just created a bunch of unusable BS because I was essentially copy and pasting formulas vs results. I ended up cutting and pasting the column between A and B so it became the new B and then hiding what was now C. I thought there was a way to copy the value of a cell vs the formula but couldn't recall it.

Thanks again

[Tony Valko]

Here's what you could do...

Inspect the formula results and make sure they are correct.

Assume the formulas are in the range C1:C10.

Select the range C1:C10
Right click>Copy
Right click>Paste Special>Values>OK
Drag C1:C10 over to B1:B10