# HOW TO CHANGE TEXT TO 1

Closed
GC - Aug 27, 2009 at 08:53 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Aug 27, 2009 at 09:15 PM
Hello,
I am trying to format a spreadsheet - the column has N.A. in it sometimes and I need it to become 1 when it appears - how do I do the foumula
have done this so far, but cannot get the last part =IFISNUMBER(FIND(("N.A.") HOW DO I GET THE VALUE TO 1
Related:

## 1 response

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Aug 27, 2009 at 09:15 PM
you must do the modification to your original formula
what is the formula which gives the N/A eerro

suppose the data from a1 to B4 are like this

a 1
s 2
d 3
f 4

in A8 you have "j"
in B8 if you have formula
=VLOOKUP(A8,\$A\$1:\$B\$4,2,0)
then B8 will get error that is #N/A

to avoid this you must have

=IF(ISNA(VLOOKUP(A8,\$A\$1:\$B\$4,2,0)),1,VLOOKUP(A8,\$A\$1:\$B\$4,2,0))

on these lines modify your macro