asked on

#VALUE! showing for empty column

I have a simple formula in Excel for extracting the initials from a name in column A to column B (below).

=LEFT(A2)&MID(A2,FIND(" ",A2)+1,1)

However, until a name is entered into A column, the B column is showing a #VALUE! error.

The first column is blank until an administrator completes it.

How can I hide this error?

ASKER CERTIFIED SOLUTION

Roy Cox

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Saqib Husain

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

matedwards

ASKER

Wow.. worked and super quick..!!

Many thanks to both of you..

Roy Cox

Pleased to help.

How do I close my question?

Shums Faruk

Hi,

IFERROR will ignore any error in cell A2 not just blank cells, Suppose your admin forgot to add space between First Name & Initial, then above IFERROR formula will return blank cell.

Try below:
=IFERROR(IF(NOT(ISBLANK(A2)),LEFT(A2)&MID(A2,FIND(" ",A2)+1,1),""),"There is no space between First Name & Initial")

Which will prompt your admin, if there is any error and ignores empty or blank cells, you may customize any text for prompt within the quotes
Check below screenshot:

matedwards

ASKER

This new system won't let me close the question no matter what I pick as Best or Assisted solution!

EE, I would like to split the points?

matedwards

ASKER

Finally.. many thanks..