0

Is there a way to process the following column A to obtain column B using the Excel IF statement (or other but preferable NOT a custom macro, I would like to just have a formula) to obtain

A        B
X        X
_EMPTY   custom_row3
Y        Y
_EMPTY   custom_row5
Z        Z

In pseudo code

 FOREACH ROW
 IF (A!='_EMPTY) 
    B=A
ELSE 
    B='custom_row'+$ROWNUM$
END

I checked the related question How to replace text of a cell based on condition in excel, but it is not clear how to extend to this case which is a bit more complicated.

Community
  • 1
  • 1
shelbypereira
  • 1,655
  • 2
  • 21
  • 42
  • 1
    You could use a formula like `=IF(A1="", "custom_row"&ROW(), A1)` in B1 and fill down as necessary. –  Dec 15 '14 at 10:17
  • This works beautifully, just what I was looking for! Note for other readers that the "," needs to be changed to a ";" – shelbypereira Dec 15 '14 at 10:35
  • The semi-colon in place of a comma would be a system regional setting. Typically, North America and many other parts of the world use a comma as a *list separator* while certain parts of Europe and some others use a semi-colon. Excel reads this from the operating system's regional settings. As you provided no sample formula, I relied upon the more predominant one in use for my example. Glad you got sorted out. –  Dec 15 '14 at 10:41

1 Answers1

0

You could use in B2

=IF(ISBLANK(A2),"custom_row"&+ROW(),A2)

and copy downward. Note a VERY IMPORTANT point. An empty cell behaves differently from a cell containing an empty string. With reference to the figure, cells A3 and A6 are empty (blank), while cell A5 contains an empty string.

enter image description here

You may need to use

=IF(OR(ISBLANK(A2),A2=""),"custom_row"&+ROW(),A2)

depending on the contents of your column A. See this relevant answer.

Community
  • 1
  • 1
sancho.s ReinstateMonicaCellio
  • 12,097
  • 14
  • 65
  • 141
  • Thanks, this is helpful as well as comment from Jeeped. In fact I had replaced blanks by BLANK to avoid this problem, but your solution is much cleaner! – shelbypereira Dec 16 '14 at 10:10