Problem removing 0

[jermsalerms]

Helper Sheet AQ2:AQ901 contain phone numbers

some have been entered 0 when there is no number provided. I am trying to write a formula that will state if AQ2 = 0 then field is blank.

in cell V2 I have tried to write all the following formulas and all result with the 0 still remaining

=IF(ISNUMBER('Helper Sheet'!AQ2)="0","",IF('Helper Sheet'!AQ2="","",'Helper Sheet'!AQ2))

=IF(ISTEXT('Helper Sheet'!AQ2)="0","",IF('Helper Sheet'!AQ2="","",'Helper Sheet'!AQ2))

=IF('Helper Sheet'!AQ2="0","",IF('Helper Sheet'!AQ2="","",'Helper Sheet'!AQ2))


any other suggestions

[Michael]

Try =IF('Helper Sheet'!AQ2=0,"",'Helper Sheet'!AQ2). HTH

Sincerely, Michael Colvin


"jermsalerms" wrote:

>
> Helper Sheet AQ2:AQ901 contain phone numbers
>
> some have been entered 0 when there is no number provided. I am trying
> to write a formula that will state if AQ2 = 0 then field is blank.
>
> in cell V2 I have tried to write all the following formulas and all
> result with the 0 still remaining
>
> =IF(ISNUMBER('Helper Sheet'!AQ2)="0","",IF('Helper
> Sheet'!AQ2="","",'Helper Sheet'!AQ2))
>
> =IF(ISTEXT('Helper Sheet'!AQ2)="0","",IF('Helper
> Sheet'!AQ2="","",'Helper Sheet'!AQ2))
>
> =IF('Helper Sheet'!AQ2="0","",IF('Helper Sheet'!AQ2="","",'Helper
> Sheet'!AQ2))
>
>
> any other suggestions
>
>
> --
> jermsalerms
> ------------------------------------------------------------------------
> jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167
> View this thread: http://www.excelforum.com/showthread...hreadid=501136
>
>

[PCLIVE]

It seems like this should work.

=IF(OR('Helper Sheet'!AQ2="0",'Helper Sheet'!AQ2="",'Helper
Sheet'!AQ2=0),"",'Helper Sheet'!AQ2)



"jermsalerms" <[email protected]>
wrote in message
news:[email protected]...
>
> Helper Sheet AQ2:AQ901 contain phone numbers
>
> some have been entered 0 when there is no number provided. I am trying
> to write a formula that will state if AQ2 = 0 then field is blank.
>
> in cell V2 I have tried to write all the following formulas and all
> result with the 0 still remaining
>
> =IF(ISNUMBER('Helper Sheet'!AQ2)="0","",IF('Helper
> Sheet'!AQ2="","",'Helper Sheet'!AQ2))
>
> =IF(ISTEXT('Helper Sheet'!AQ2)="0","",IF('Helper
> Sheet'!AQ2="","",'Helper Sheet'!AQ2))
>
> =IF('Helper Sheet'!AQ2="0","",IF('Helper Sheet'!AQ2="","",'Helper
> Sheet'!AQ2))
>
>
> any other suggestions
>
>
> --
> jermsalerms
> ------------------------------------------------------------------------
> jermsalerms's Profile:
> http://www.excelforum.com/member.php...o&userid=30167
> View this thread: http://www.excelforum.com/showthread...hreadid=501136
>

[Dave Peterson]

=if('helper sheet'!aq2=0,"",'helper sheet'!aq2)

maybe????

jermsalerms wrote:
>
> Helper Sheet AQ2:AQ901 contain phone numbers
>
> some have been entered 0 when there is no number provided. I am trying
> to write a formula that will state if AQ2 = 0 then field is blank.
>
> in cell V2 I have tried to write all the following formulas and all
> result with the 0 still remaining
>
> =IF(ISNUMBER('Helper Sheet'!AQ2)="0","",IF('Helper
> Sheet'!AQ2="","",'Helper Sheet'!AQ2))
>
> =IF(ISTEXT('Helper Sheet'!AQ2)="0","",IF('Helper
> Sheet'!AQ2="","",'Helper Sheet'!AQ2))
>
> =IF('Helper Sheet'!AQ2="0","",IF('Helper Sheet'!AQ2="","",'Helper
> Sheet'!AQ2))
>
> any other suggestions
>
> --
> jermsalerms
> ------------------------------------------------------------------------
> jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167
> View this thread: http://www.excelforum.com/showthread...hreadid=501136

--

Dave Peterson

[Gary''s Student]

If you want the zero to appear blank, then select the cell and pull-down:

Format > Cell > Number > Custom and enter #,### in place of general
--
Gary's Student


"jermsalerms" wrote:

>
> Helper Sheet AQ2:AQ901 contain phone numbers
>
> some have been entered 0 when there is no number provided. I am trying
> to write a formula that will state if AQ2 = 0 then field is blank.
>
> in cell V2 I have tried to write all the following formulas and all
> result with the 0 still remaining
>
> =IF(ISNUMBER('Helper Sheet'!AQ2)="0","",IF('Helper
> Sheet'!AQ2="","",'Helper Sheet'!AQ2))
>
> =IF(ISTEXT('Helper Sheet'!AQ2)="0","",IF('Helper
> Sheet'!AQ2="","",'Helper Sheet'!AQ2))
>
> =IF('Helper Sheet'!AQ2="0","",IF('Helper Sheet'!AQ2="","",'Helper
> Sheet'!AQ2))
>
>
> any other suggestions
>
>
> --
> jermsalerms
> ------------------------------------------------------------------------
> jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167
> View this thread: http://www.excelforum.com/showthread...hreadid=501136
>
>

[jermsalerms]

I tried PCLIVE's and it worked so Im sticking with it.