0

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?

Barbara
  • 134
  • 2
  • 10
  • 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
  • @ScottCraner Wow, this is so strange, I keep getting the same error instead. May I ask you if you could upload your excel file somewhere so I can have a look and play with it? – Barbara Sep 12 '19 at 18:31
  • I downloaded yours and when it translated the formula you were using AVERAGE not MID. I am not sure what language you are using but it seems as if you mistranslated MID to the wrong function in your language. – Scott Craner Sep 12 '19 at 18:32
  • 1
    You should be using `STRINGA.ESTRAI` not `MEDIA` – Scott Craner Sep 12 '19 at 18:35
  • @scottcraner Duh! You're right. I got it from a website that listed a wrong translation. If you want to change your comment to an answer I'm going to mark it as accepted. Thank you! – Barbara Sep 12 '19 at 18:35
  • Nah, was a typo. Glad to have helped. – Scott Craner Sep 12 '19 at 18:36
  • @Barbara,, [check this link](https://superuser.com/questions/1481591/excel-mid-function/1481774#1481774) recently I've solved almost the same issue,, in used formula you need to replace `*` with `_` only and it work for you. – Rajesh Sinha Sep 13 '19 at 06:21
  • @RajeshS thank you! As Scott explained it was actually a mistranslation (see comment 7). I'm using a non-US version of excel, I got the translation of MID from https://www.excel-function-translation.com/ but it was wrong. – Barbara Sep 13 '19 at 12:47
  • Solved. See [Scott's comment](https://superuser.com/questions/1481917/split-string-based-on-delimiter-value-error?noredirect=1#comment2235141_1481917). Thank you @scottCraner. – Barbara Sep 13 '19 at 12:49

0 Answers0