3

I have a fairly complex (to me at least) nested IF(ISNUMBER formula that I would like to wrap or include SUBSTITUTE to remove "" from the result of the formula and replace the "" with a space " ".

I asked a similar question on here when the formula was rather basic but as that one had been gratefully answered I thought a new question was appropriate.

The formula below is entered in cell B1 and looks at a string in cell A1 that contains both letters and numbers and looks for an occurrence within the string of either the word Wav, mp3, flac or mogg. The formula then runs the nested formula that finds and displays extra information pertinent to either the word Wav, mp3, flac or mogg.

This all works fine although I know the formula is not elegant but the string it is pulling its results from has "_" separating all of the words within the string which I am trying to replace in the formula result with a simple space " ".

The string looks like Artist_-_Album_Title_[Source]_(14_Tracks)_44.1_kHz_16_bit_Wav

The formula is actually extracting the "16_bit" part which and also be 32, 64 or 128_bit and I need the result to be "16 bit" and so on.

Main Formula =IF(ISNUMBER(SEARCH("wav",A1)),MID(A1,SEARCH("kHz",A1)+4, SEARCH("Wav",A1)-SEARCH("KHz",A1)-5),IF(ISNUMBER(SEARCH("flac",A1)),MID(A1,SEARCH("kHz",A1)+4, SEARCH("flac",A1)-SEARCH("KHz",A1)-5),IF(ISNUMBER(SEARCH("mp3",A1)),MID(A1,SEARCH("kHz",A1)+4, SEARCH("mp3",A1)-SEARCH("KHz",A1)-5),IF(ISNUMBER(SEARCH("mogg",A1)),MID(A1,SEARCH("kHz",A1)+4, SEARCH("mogg",A1)-SEARCH("KHz",A1)-5),"NO"))))

To complicate things more I am using the below to check if the cells in column A are blank or not and if not the above formula is run but if A1 is blank it does nothing.

=IF(A1="","",SUBSTITUTE(A1,"_"," "))

As I say, individually they both work fine but this is well above my skill level and I am unable to combine the two?

1 Answer 1

4

Eat this elephant one bite at a time.

You have a formula that works. Next, wrap the Substitute() function around your formula.

=SUBSTITUTE(YourWorkingFormula,"_"," ")

When you edit the formula, use Alt+Enter to break into a new line and better identify elements of the formula

=SUBSTITUTE(
IF(ISNUMBER(SEARCH("wav",A1)),MID(A1,SEARCH("kHz",A1)+4, SEARCH("Wav",A1)-SEARCH("KHz",A1)-5),IF(ISNUMBER(SEARCH("flac",A1)),MID(A1,SEARCH("kHz",A1)+4, SEARCH("flac",A1)-SEARCH("KHz",A1)-5),IF(ISNUMBER(SEARCH("mp3",A1)),MID(A1,SEARCH("kHz",A1)+4, SEARCH("mp3",A1)-SEARCH("KHz",A1)-5),IF(ISNUMBER(SEARCH("mogg",A1)),MID(A1,SEARCH("kHz",A1)+4, SEARCH("mogg",A1)-SEARCH("KHz",A1)-5),"NO")))),
"_",
" ")

Now, wrap the IF Statement around that. You can use spaces to indent parts of the formula

=IF(A1="","",
   SUBSTITUTE(
      IF(ISNUMBER(SEARCH("wav",A1)),MID(A1,SEARCH("kHz",A1)+4, SEARCH("Wav",A1)-SEARCH("KHz",A1)-5),IF(ISNUMBER(SEARCH("flac",A1)),MID(A1,SEARCH("kHz",A1)+4, SEARCH("flac",A1)-SEARCH("KHz",A1)-5),IF(ISNUMBER(SEARCH("mp3",A1)),MID(A1,SEARCH("kHz",A1)+4, SEARCH("mp3",A1)-SEARCH("KHz",A1)-5),IF(ISNUMBER(SEARCH("mogg",A1)),MID(A1,SEARCH("kHz",A1)+4, SEARCH("mogg",A1)-SEARCH("KHz",A1)-5),"NO")))),
      "_",
      " ")
)
2
  • Outstanding, thank you and I have learnt a lot from your answer.
    – Kenny
    Jun 9, 2020 at 0:45
  • One of those answers whose quality elevates the question it's attached to. Excellent. +1
    – Alex M
    Jun 9, 2020 at 20:06

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .