My ‘RIGHT / FIND’ function works but I don’t know why

DLB20720 · Jan 20, 2014

Excel 2013 - I inserted a function in my spreadsheet that, after experimenting with, I got it to work but I don’t understand why and I’m hoping someone can explain this to me. I would eventually like to expand or change this function but know I can’t do that until I understand how it works. Actually, I’ve tried and failed and I assume it is because of my lack of understanding on how this function works to begin with.

My question; I’m importing data into a worksheet that shows changes in stock price targets that can appear in 2 different ways depending on whether coverage exists, or is being initiated. I want to extract the new price in both cases which will always be on the right. These are the 2 formats I normally see;

$100 >> $120
$120

Both formats appear in my worksheet column ‘C’ and I started with this formula in column ‘D’ =RIGHT(C2,LEN(C2)-FIND("$",C2,3)) but that only worked on cells which showed a change in price ($100 >> $120). The cells which have only 1 value ($120) returned an error so I used this formula to simply return the single value =IFERROR(RIGHT(C2,LEN(C2)-FIND("$",C2,3)),C2) This last formula returns the correct values regardless of format but there are 2 things I don’t understand;

Why didn’t the 1^st formula work on the cells with 1 value? It should read from the right, find the $ sign and return values to the right of that, but it doesn’t work on the cells with single values. I can’t figure out why it works on cells with 2 values, but not 1. That doesn’t make any sense to me at all.
The number ‘3’ in both of the above formulas should represent the ‘start number’ of the character to the right of the $ sign, if I’m understanding the formula correctly. In my case I initially assumed this should be ‘1’ but that didn’t work so after experimenting I found that ‘3’ returns the values I want every time; I just can’t figure out why.

Everything I’ve read about using the RIGHT & FIND function together tells me my formulas should not work, but they do and I’m hoping someone can explain why. "Why does it work" is a strage question on this forum!

Thanks.

Jonmo1 · Jan 20, 2014

In cells with only 1 value
$120
FIND("$",C2,3)
The 3 there says to begin looking for the $ at the 3rd character of the string.
There are no $'s in the string after the 3rd character.
The Find function returns the #VALUE! Error when it can't find what it's looking for.

So the IFERROR function corrects for that.
If the FIND is returning an error (there is only 1 $ in the string), then IFERROR says to just return the contents of C2 (the single value)

Hope that helps.

DLB20720 · Jan 20, 2014

Thanks for your response.

You lost me with where the function begins looking for the $ sign. From everything I’ve read, the function should start reading from the right side of the target cell, find the ($) value and then return values to the right of that using the starting character I indicate (I used 3 but thought it should have been 1). From what you indicated, the function would begin looking for the $ sign anywhere AFTER the 3^rd character but even when I use 5 characters I still can’t get this to work.

I just tried the formula (on the single value) with anywhere between 1 and 5 digits but it still returns the #VALUE! error regardless. But on the cells with 2 values, I can use any combination of numbers, with or without decimals, and it returns the correct values. I am obviously missing something with the function in a cell containing a single value.

Jonmo1 · Jan 20, 2014

The FIND function searches from Left to Right.

So with the string
$120
FIND("$",C2,3)
It begins looking for $ from the 3rd character (which is the 2)
And there are no $'s in the string from the 3rd character (the 2) and beyond (to the right)
So it can't find what it's looking for = #VALUE! error.

DLB20720 · Jan 20, 2014

Okay, I think I understand about half of this now. I had to research the FIND function separate from its use with RIGHT / FIND and see there are some differences on how it references and returns characters. I think.

I’m going to try to write a formula using only the FIND function for single values and then I can use that in combination with IFERROR so my formula will read both values. If successful, that will allow me to expand the use of the data in my ‘C’ cells.

Thanks Jonmo1

Jonmo1 · Jan 20, 2014

You're welcome.

I think your confusion is that the RIGHT function uses the characters from the right of the value.
But the actual Error (#VALUE!) was coming from the FIND function.
So the overal formula would translate like this

=IFERROR(RIGHT(C2,LEN(C2)-FIND("$",C2,3)),C2)
FIND("$",C2,3) = #VALUE! if there is no $ after the 3rd character.
So it becomes
=IFERROR(RIGHT(C2,LEN(C2)-#VALUE!),C2)
LEN(C2) = say 4
=IFERROR(RIGHT(C2,4-#VALUE!),C2)
4-#VALUE! - excel doesn't know what to do with this, Number-Error = Error
=IFERROR(RIGHT(C2,#VALUE!),C2)
Now the RIGHT function doesn't know what to do with the #VALUE! Error either.. = Error
=IFERROR(#VALUE!,C2)
The Iferror function DOES know what to do with an error, so it returns the contents of C2

You can use the Formula Evaluation Tool to see this happen.

While developing your formula, I would suggest breaking out all the steps into seperate cells like
D2: =FIND("$",C2,3)
E2: =LEN(C2)
F2: =RIGHT(C2,E2-D2)
This will make it much easier to spot problems.

Once it all works, THEN work backwards and combine each step into the end formula.

Hope that helps.

DLB20720 · Jan 20, 2014

You are correct that I assumed the function used the characters to the right of the value.

I’m getting most of this now, thanks, and have a formula that is returning correct values although I’m getting the $ sign back from my single value cells. That should be a minor tweak in one of the formula numbers.

I also noticed that I had to have my ‘C’ cells formatted to ‘Text’ otherwise the function does not work correctly. That actually may explain part of the reason I couldn’t get anything to work yesterday (as I was slamming my head against the wall). I can have $120 in a ‘C’ cell but if it is formatted as currency, the formula does not work. As soon as I change the format to ‘Text’ for the ‘C’ cell, the cell with the formula display the correct results.

Jonmo1 · Jan 20, 2014

A cell formatted as currencly doesn't actually contain a $.
The $ is just added to the way it displays the number in the cell.

Look closely,
With a cell formatted as currency, enter $120 into that cell.
Then select that cell, and look at the formula bar. There is no $ in the cell.

When the cell is formatted as Text, then you're just entering a text string that happens to begin with a $

DLB20720 · Jan 20, 2014

I knew that about a cell with a single $ value but I had never looked at a cell containing anything like I am using ($100 >> $120). Even with the cell formatted to ‘Currency’, the cell will display the $ signs. The more I get into this the more I believe that is one reason some of the formulas I attempted yesterday did not work. I am pulling these from a Power Query worksheet where the cells are formatted as ‘General’ but I never thought to look at how they loaded in the new sheet. I probably had the entire column set as Currency.

There are many things with Excel that I have learned simply by beating my head against a wall repetitively until I understand how it works. This is one of them. Thanks for your help Jonmo1.

Jonmo1 · Jan 20, 2014

You're welcome..

$100 >> $120 shows the $'s in the cell because it isn't a number
It's just a text string because it contains non numeric characters (>>).
Therefor the currency format cannot be applied and it is treated as text.
Just because a cell is 'formatted' as currency, doesn't mean the cell actually contains an actual currency value.

This is actually a good learning experience.

Just because a cell is formatted in a cerain way, does not mean that cell actually contains a proper value of that format.
You can format a cell as a Date, but enter "Hello dude", and that's what the cell will display.
Only if you enter a true Date Value will the Date format be applied to that value.

Also important to note..

If a cell contains a value of one type, simply changing the format of the cell to something else will not change the actual value contained in that cell.

Formatting is just the method Excel uses to 'display' the value in cell.
What is shown in the Formula Bar is what the cell actually contains.

My ‘RIGHT / FIND’ function works but I don’t know why

DLB20720

Board Regular

Excel Facts

Jonmo1

MrExcel MVP

DLB20720

Board Regular

Jonmo1

MrExcel MVP

DLB20720

Board Regular

Jonmo1

MrExcel MVP

DLB20720

Board Regular

Jonmo1

MrExcel MVP

DLB20720

Board Regular

Jonmo1

MrExcel MVP

Similar threads

Forum statistics

Share this page

My ‘RIGHT / FIND’ function works but I don’t know why

Board Regular

Excel Facts

MrExcel MVP

Board Regular

MrExcel MVP

Board Regular

MrExcel MVP

Board Regular

MrExcel MVP

Board Regular

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock