2

I have a Google Sheet where an array formula in Column I creates a video name based on the content entered into columns A+B+C+D.
Column C is the number of the video. For some videos, I need a leading zero in the name and for others, not. I don't want to format column C as text - I need to leave this column formatted as a number. I also had to format Column C to add a full stop after the number as it is important this is part of the number in the name. I can't rely on editors to always remember to add the full stop.

What do I add to the array formula so it correctly reflects the content in Column C, where some videos will have a leading zero (like 01. and some not, like 0.)

=ARRAYFORMULA(IF($E2:$E="Yes", $D2:$D, IF(ISBLANK($A2:$A), "", $A2:$A & " - ") & IF(ISBLANK($B2:$B), "", $B2:$B & " - ") & IF(ISBLANK($C2:$C), "", $C2:$C & ". ") & $D2:$D))

Here is my Google Test Sheet. https://docs.google.com/spreadsheets/d/1AIdZmFqtojG_7OLY_twJhmFewVTyoFAsozzbeSeHun4/edit?usp=sharing

Improve this question

1 Answer 1

Reset to default
0

You can use either the following formulas instead

=ARRAYFORMULA(IF($E2:$E="Yes", $D2:$D, IF(ISBLANK($A2:$A), "", $A2:$A & " - ") & IF(ISBLANK($B2:$B), "", $B2:$B & " - ") & IF(ISBLANK($C2:$C), "", TEXT($C2:$C,"00. ")) & $D2:$D))

OR 

=ARRAYFORMULA(IF($E2:$E="Yes", $D2:$D, IF(ISBLANK($A2:$A), "", $A2:$A & " - ") & IF(ISBLANK($B2:$B), "", $B2:$B & " - ") & IF(ISBLANK($C2:$C), "", TEXT($C2:$C,"00") & ". ") & $D2:$D))

(Either of the above formulas will format all single digit numbers in column C having a leading 0. This will provide even better consistency to your results than the one you are asking for, as in "Big battles! - 01. Bass drum" instead of "Big battles! - 1. Bass drum")

Improve this answer
8
  • Thank you. Unfortunately, this formula forces all the videos to have a leading zero. Not all videos are supposed to have a leading zero. Only those where there is a leading zero in Column C.
    – user246475
    May 16, 2020 at 5:09
  • We only use leading zeros where there is a collection of videos in excess of 9.
    – user246475
    May 16, 2020 at 5:10
  • This note is at the top of the column in my test sheet - How do I change the array formula so it reflects the formatting of the number in column C? For example, The Arts Unit @home Creative Classes - Introduced Species - 1. Interview with Katy Abbott should have a zero in front of the 1 so it displays as 01. rather than 1. Not all videos have a leading zero, only those idenfitied in column C with a leading zero should have a leading zero.
    – user246475
    May 16, 2020 at 5:12
  • I am afraid you will not be able to accomplish that without manual intervention. That is you cannot have only some videos display the leading zero. To enjoy the benefits of automation you need to slightly divert from your way of naming the videos. Also, as mentioned in my answer, I think it is a more consistent way of naming them. Things can get really messy down the line. Please trust me on that. I have been archiving masses of work for years.
    – marikamitsos
    May 16, 2020 at 6:18
  • Thanks for your help. I agree with you but unfortunately, I don't have a say in this convention. I won't be able to manually change the data appearing in column I with the array formula as it is copied from the top down. I think in this case I will simply have to change column C to a text column. This will ensure column I with the array formula displays the data as required. This spreadsheet exists to help editors name videos. Now that I think about it, once the video is named it moves into another Excel sheet where the numbers become part of the name and are only read as text anyway.
    – user246475
    May 16, 2020 at 7:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.