My string is separated by an indefinite and variable number of underscores. "SOMETEXT_MORETEXT_ABCD_DEF_..." I need to put each word inside a different column. I've found these links which explain exactly what I'm trying to do.

The formula is =TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",999)),COLUMNS($A:A)*999-998,999))

I've used the exact same formula and changed the references and the delimiter but it doesn't work and gives me a #value error.

My file: https://app.box.com/s/q21pc0r04sqvf4ewdjw6b1cmtu6o0cnn

I don't understand why there's this error. Can anybody help me to fix it?

  • 1
    Use text to columns. It is found in the data tab. – Scott Craner Sep 12 '19 at 14:21
  • Thanks Scott. I can't do that because it requires the user's input at some point. It needs to be automated because the file will be fed to a software eventually. – Barbara Sep 12 '19 at 15:15
  • @ScottCraner you can find the example I'm working with at the link above (on box.com) – Barbara Sep 12 '19 at 17:37
  • @ScottCraner A1 is "WORD,RED,ORANGE,RULER" and B1 is =TRIM(MID(SUBSTITUTE($A1;",";REPT(" ";999));COLUMNS($A:A)*999-998;999)). There's a ; because I'm using a non-US version of excel. – Barbara Sep 12 '19 at 18:23
  • 1
    You should be using STRINGA.ESTRAI not MEDIA – Scott Craner Sep 12 '19 at 18:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.