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. Sep 12, 2019 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, 2019 at 15:15
  • @ScottCraner you can find the example I'm working with at the link above (on box.com)
    – Barbara
    Sep 12, 2019 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, 2019 at 18:23
  • 1
    You should be using STRINGA.ESTRAI not MEDIA Sep 12, 2019 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.