Vlookup one more time

  • Thread starter Thread starter RyneFan
  • Start date Start date
R

RyneFan

I am using the following formula to get a value from a cut and pasted web site.
This formula finds the value I'm looking for, takes the / out of the text and
then turns it into a number. The next thing I need for it to do is instead of
coming back as #N/AI'd like it to be a zero. I know how to do all of this
sepertaly, but can't put it together. Once I get this the worksheet will be
done.

Thanks in advance. I've solved a lot of my problems on this site.

=--MID(VLOOKUP(,,,),1,FIND("/",VLOOKUP(,,,),1)-1)
 
Hi
try the following
=IF(ISNA(--MID(VLOOKUP(,,,),1,FIND("/",VLOOKUP(,,,),1)-1),0,--MID(VLOOK
UP(,,,),1,FIND("/",VLOOKUP(,,,),1)-1))
 
Hi


=IF(ISERROR(VLOOKUP(,,,)),0,--MID(VLOOKUP(,,,),1,FIND("/",VLOOKUP(,,,),1)-1)
)

But I have a feeling you surely can do it in much simpler way - usually you
don't need formulas at all for such tasks. Some examples.

When you have entries like '123/' or '321/456', and you want to divide them
into numbers like 123 or 321 and 456, then:
Make it sure you have enough free columns right at column with your data:
Select the column;
From Data menu, select 'Text to Columns';
Select 'Delimited' and press 'Next';
Check Other, enter '/' for delimiter and press 'Finish'. It's all!

When you have entries like '321/456', and you want to turn them to numbers
like 321456, then:
Select the column
From Edit menu, select 'Replace';
Into 'Search for' field enter '/', leave 'Replace with' field empty, and
click on 'Replace All';
Convert column to numbers as I described in my answer to your previous
posting.
 
Back
Top