Excel Nested If -- help, please!

[Irmgard Wesselb]

Hi,

I am trying to complete a nested If function, but have not been successful --but at least it partially works. The attached spreadsheet shows what I want to achieve in column C,
i.e.
If Column A contains any number except 999
and Column B is not empty, then concatenate (put in spaces)

If Column A = 999, then just copy the number as is

If Column B is blank, put in nothing (empty string)

The formula I have so far:
=IF(A2<>999,IF(B2<>"","0"&LEFT(B2,1)&" "&MID(B2,2,4)&" "&RIGHT(B2,4)),B2)

handles things correctly if there is a number in Column B, but it puts in a 0 (Zero) or FALSE, depending which number is in Column A.

Any help would be greatly appreciated.

Regards,
Irma Wesselbaum

View attachment Nested If.xls

 

[Kiwiman]

Howzit

Try...

=IF($B2="","",IF($A2<>999,"0"&LEFT($B2,1)&" "&MID($B2,2,4)&" "&RIGHT($B2,4),$B2))

 

[Brianwarnock]

Irma be careful not to confuse Blank " " and Empty "" cells,
Excel doesn't help as the IsBlank function actually tests for empty cells

Brian

 

[Irmgard Wesselb]

Dear Brianwarnock,

Thank you for pointing out the difference between blank and empty cells.
The data I got contains empty cells, so the formula received from Kiwiman works perfectly: =IF($B2="","",IF($A2<>999,"0"&LEFT($B2,1)&" "&MID($B2,2,4)&" "&RIGHT($B2,4),$B2))

Thanks again,
Irmgard Wesselbaum