How to make a cell display nothing if nothing is in the cell its referring to.

[Nexion21]

I posted this a few days ago and got a quick response, but the formula that I was using didn't work for a few things, so I was forced to change my formula.

I used to have this formula: =IF(D5="","",25+5*SUMPRODUCT(--(MID(D5,ROW(1:255),1)="*"))) which, if D5 was empty, would make the cell containing the formula display nothing.

I am now using this formula: =IF(LEN(A5)-LEN(SUBSTITUTE(A5,"*",""))=3,40,IF(LEN(A5)-LEN(SUBSTITUTE(A5,"*",""))=1,30,IF(LEN(A5)-LEN(SUBSTITUTE(A5,"*",""))=2,35,IF(LEN(A5)-LEN(SUBSTITUTE(A5,"*",""))=4,45,IF(LEN(A5)-LEN(SUBSTITUTE(A5,"*",""))=5,50,25))))), which does the exact same thing, but still displays the final value (25) even if nothing is in the cell it is referring to.

I've tried adding IF(A5="","",(LEN...25))))), IF(A5="","",LEN...25)))))) and neither have worked.

[Craig K.]

try this

Please Login or Register  to view this content.

[Nexion21]

EDIT: That displays nothing when there is something other than an asterisk in the cell. I need it to be blank when there is absolutely nothing in the cell, not just when there isn't an asterisk.

[AlKey]

Hi Mexion21

I think the problem with your formula is the last part ,IF(LEN(A5)-LEN(SUBSTITUTE(A5,"*",""))=5,50,25)))))

Replace 25 with "" and see if this will work.

[Nexion21]

I need the 25 there so it displays 25 when I have a stock number but no asterisks. Example:

M3353 | 25
M5949 * | 30
M3533 ** | 35

[Craig K.]

The formula I provided is blank when there is nothing inside of the A5 cell. Have you tried checking the formatting of your cells?

[Nexion21]

You are correct, it is blank when there is nothing inside the cell, but it is also blank when there is no asterisk. It's turning up like this:

M3353 | ____ (Where _ = nothing)
And I need it to show up like this:

M3353 | 25

[Craig K.]

You didn't make clear what the cells were going to be populated with. Next time be a little more descriptive in your post please.

Try this then

Please Login or Register  to view this content.

[Nexion21]

Sorry about the descriptiveness, but thank you for your help! It's working now.

[Craig K.]

I'm glad I could help.

[AlKey]

It looks like this whole thing could've been done with simple formula like this:

=IF(A5="","",TRIM(RIGHT(A5,LEN(A5)-FIND("|",A5))))

at least based on the data samples provided.