Number to text

[MN_Excel]

For v-lookup purposes I need a number field to display as a text. Simply choosing the text option doesn't seem to work. Any ideas on how to convert without having to add "+0" to the end?

 

[T. Valko]

For v-lookup purposes I need a number field to display as a text. Simply choosing the text option doesn't seem to work. Any ideas on how to convert without having to add "+0" to the end?

Not real clear what you're asking.

Maybe this...

A1 = some NUMERIC number

To convert that to a TEXT number:

A1&""

 

[shg]

Sleect the column and do Data > Text to columns, Next, Next, select Text, Finish.

 

[MN_Excel]

Basically, I'm doing a v-lookup and the return value is a blank cell, but rather than display a blank cell is it returning a zero.

 

[scottylad2]

Basically, I'm doing a v-lookup and the return value is a blank cell, but rather than display a blank cell is it returning a zero.

Try =IF((YourVlookupHere)="","",YourVlookupHere)

 

[MN_Excel]

That was my thought too. The vlookup is already nestled in an "IF(ISNA(" formula. I think it was something to do with the format of the cells in the lookup range, but I can't quite figure it out.

Thanks for your input.

 

[scottylad2]

Can you post the whole formula? it's nothing to do with formating

 

[MN_Excel]

=IF(ISNA(VLOOKUP(I381,'Sales!$C$1:$J$17000,8,FALSE)),"",(VLOOKUP(I381,'Sales Returns!$C$1:$J$17000,8,FALSE))).

The reason I think it is formatting is because I added new data to the range today and it is only that data coming back as zero. Data from previous months is properly coming back as blank.

 

[scottylad2]

Try this

=IF(OR(ISNA(VLOOKUP(I381,'Sales!$C$1:$J$17000,8,FALSE)),VLOOKUP(I381,'Sales!$C$1:$J$17000,8,FALSE)=0),"",VLOOKUP(I381,'Sales Returns!$C$1:$J$17000,8,FALSE))

 

[T. Valko]

Try this

=IF(OR(ISNA(VLOOKUP(I381,'Sales!$C$1:$J$17000,8,FALSE)),VLOOKUP(I381,'Sales!$C$1:$J$17000,8,FALSE)=0),"",VLOOKUP(I381,'Sales Returns!$C$1:$J$17000,8,FALSE))

There's a problem with the sheet name(s) ?

You have to use nested IFs. Otherwise, if the lookup throws an #N/A error the 2nd OR argument will also throw an #N/A error.

ISNA will be TRUE and the 2nd lookup will = #N/A so OR will = #N/A.

=IF(OR(TRUE,#N/A), ... = #N/A