0

I have tried the function SPLIT with the argument "" and it does not work.

I have a list that is phylogenetic code, so in the source file it's structured like: 10??12103??? etc., and I want to insert it into a Google sheet and split it to line up with some precoded materials for other taxa.

1

SPLIT will work:

=SPLIT(A2,"?",0,1)

Here, SPLIT views the split character (i.e, "?") as the same whether there is 1, or whether there are 500 of them in a row.

Of course, this can be applied to an entire column as well:

=ArrayFormula(IF(A2:A="","",SPLIT(A2:A,"?",0,1)))

Your post shows "?" as the non-numeric characters. However, you may simply have meant that those question marks stand for any of various non-numeric characters. In that case, use this:

=SPLIT(REGEXREPLACE(A2:A,"[^0-9]","?"),"?",0,1)

...or for the whole column...

=ArrayFormula(IF(A2:A="","",SPLIT(REGEXREPLACE(A2:A,"[^0-9]","?"),"?",0,1)))
| improve this answer | |
0

Each line of your data file is 12 characters long, and consists of four elements: two digits, twp question marks, five digits and three question marks. The question marks are separators.

SPLIT is not a good option in this case because the separators are not consistent. Try MID instead.

  • =value(mid(A1,1,2)) - will return the first two characters
  • =value(mid(A1,5,5)) - will return the five character sequence

If you use MID alone, it will return a string. I have wrapped the formula in the VALUE function in order to return a value. You can ignore this if the string is all you need.

| improve this answer | |

Your Answer

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

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