Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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).


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 “-”.


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 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

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
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


share|improve this answer

Your Answer


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