Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I’m trying to create a VBA formula and not macro that picks the “ID’s” instead of “-” (dashes). The image below is an example of what I’m trying to do, for each row I want to pick the ID (number or text, written in cells where there aren't dashes).

a

In other words, I need a formula that picks ID from a selected row (i.e. [F17:P17]). The formula will delivery the value of the cell that doesn't contain “-”.

Example:

IF(logical_test;”pick a ID (number or text)”;”-”)

Unfortunately there’s not a formula that does the opposite job as IF, kind of IFNOT. IFNA doesn't help either.

share|improve this question

migrated from stackoverflow.com Mar 26 '13 at 2:38

This question came from our site for professional and enthusiast programmers.

    
Are you guaranteed that any non-dash from columns F to P will be a repetition? Or are you using G through P as the source and F as the answer? –  rajah9 Mar 25 '13 at 16:42
    
Not sure what you are trying to achieve but there is a of doing IFNOT. Try: IF(NOT(test),true,false) –  Dave Sexton Mar 25 '13 at 16:48

2 Answers 2

In I17, put =CONCATENATE(C17,D17,E17,F17,G17,H17). This will give you something like ----1282525-.

Then in J17, put =SUBSTITUTE(I17,"-",""). This will remove the dashes, leaving you the single non-dash answer.

You may then fill down the formulas in these columns.

share|improve this answer
1  
Thanks rajah9, worked beautifully. I combined both formulas you gave me to be like: =SUBSTITUTE(CONCATENATE(G17;H17;I17;J17;K17;L17;M17;N17;O17;P17);"-";"") –  Jacob Zucula Mar 25 '13 at 16:56

This formula will give you the last value in the row that isn't a dash

=LOOKUP(2,1/(G17:P17<>"-"),G17:P17)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.