IFNA function and text into value

Hi all,

Really hope somebody can help.
(I am using excel 2016)

I am using a big excel file and I use the below formula in excel.

=IFNA(VLOOKUP($A:$A,'Loonstaat WN 2017'!$A$12368:$Q$12448,4,0),0)

However the VLOOKUP RETURNS : 15.972,22 and this is seen as text.
I was hoping that adding *1 after the formula would make the field a value.

But unfortunately that does not work.
Does anybody know how to make the result of the above mentioned formula into a Value.
And by that I mean it needs to be in the formula.


Thanks!

=IFNA(--SUBSTITUTE(VLOOKUP($A:$A,'Loonstaat WN 2017'!$A$12368:$Q$12448,4,),".",""),)

Super thanks!

Hi tim,

Thanks for your reply.
Somehow the formula worked on my home computer but now when I am working at work
the formula gives a # VALUE! error again.

I have used your formula:
=IFNA(--SUBSTITUTE(VLOOKUP($A:$A,'Loonstaat WN 2017'!$A$12368:$Q$12448,4,),".",""),)

I am using excel 2016 at work
Can it be that some regional settings on my computer need to be different?
My decimal symbol is a "."
My digit grouping symbol is ,

It is so strange hope you have the golden tip!

Hi Tim,

So the text I have is:

15.972,22

My value needs to be:

15972.22

This for my decimal symbol is a "." and my thousands symbol is a ","

Maybe you have differences between System decimal point/Digit grouping symbol and Excel decimal separator/thousands separator (at home/at work)

I tried this:

=SUBSTITUTE(IFNA(SUBSTITUTE(VLOOKUP($A:$A,'Loonstaat WN 2017'!$A$12368:$Q$12448,4,),".",""),),",",".")

But then my SUM formula is not adding now
15972.22+15972.22

I tried this:

=SUBSTITUTE(IFNA(SUBSTITUTE(VLOOKUP($A:$A,'Loonstaat WN 2017'!$A$12368:$Q$12448,4,),".",""),),",",".")

But then my SUM formula is not adding now
15972.22+15972.22

What will happen if you put into cell =15972.22 ?
What will happen if you put into cell =15972,22 ?
and which one is working at home? (I assume you are at work now)

you can try

Formula:

Please Login or Register  to view this content.

Hi Joris

With regards to:
have you looked at my suggestion to use the VALUE function instead of SUBSTITUTE.

I tried that one as well but somehow it does not work it gives me a #VALUE! error there as well.
That is why I was trying the substitute function

Hi Sandy my system settings in excel have been set up
so excel is using my system settings

thousands is ","
and decimal is "."
is shown in my regional settings of computer
and also under advanced options in excel

the setting in excel says use system settings and they are the same

If I use that one I get a #VALUE! error

strange right.
i really do understand what you are all saying but so strange

is it possible to add example xlsx file with this error ?

yes but I do noy see the option where to add the file
let me have a look I will add a file

I have added the file

No, you don't

Here is the file I hope

try

Formula:

Please Login or Register  to view this content.

Of course if you don't want #N/A "error" you can wrap formula with IFNA()

here it is Joris

Hi Sandy,

I did this now:

=IFNA(--SUBSTITUTE(SUBSTITUTE(VLOOKUP($A:$A,'Loonstaat WN 2017'!$A$12368:$Q$12448,4,),".",""),",","."),0)

But then if there is an "empty" field I get a #VALUE error?
So it is working but for the fields where there is no text or value I get a #VALUE error.

How can I solve that?
Thanks! Highly appreciated!

Hi Sandy,

I did this now:

=IFNA(--SUBSTITUTE(SUBSTITUTE(VLOOKUP($A:$A,'Loonstaat WN 2017'!$A$12368:$Q$12448,4,),".",""),",","."),0)

But then if there is an "empty" field I get a #VALUE error?
So it is working but for the fields where there is no text or value I get a #VALUE error.

How can I solve that?
Thanks! Highly appreciated!

here is attachment
check yellow and blue cellls

That is the one!

Super
Thanks
the IFERROR solves it!

Highly appreciated!!!!

Super thanks
Is solved now (closed)

Glad to help

Better is:

If the problem is solved it's always a good practice to show respect to the person(s) who have helped you is a click on the Add Reputation first and then mark the thread as SOLVED (Thread Tools), Thanks