1

I have string ( "-" ) delimited data (alphanumeric, variable length) in a single column which has the format...

Column A
"aaa-bbb-ccc"
"ddd-bbb-eee"
"aaa-fff-ggg"

I have been able to use array_constrain() to return partial data elsewhere but this is N elements from the beginning of the array to the end of the array so I could have...

aaa               (num_cols = 1)
aaa-bbb           (num_cols = 2)
aaa-bbb-ccc       (num_cols = 3)

I am looking to get the last N elements from the split data so...

bbb-ccc   OR 
ccc

num_cols only goes from the beginning of the array to the back of the array so that's no good for my scenario.

This answer to a similar question suggests using regexextract() to retrieve the last value which if my RegEx Foo was stronger then perhaps I could make that work with a little nudge in the right direction.

I know that index() can be used but that only returns one element at a time from the split data so that gets messy / inefficient.

So the question is does anybody know how to return the last N elements from an array? Ordinary sorting wouldn't work but reversing the array could work.

Community
  • 1
  • 1
klkl
  • 323
  • 4
  • 13
  • I don't know if this will work in google sheets or if I understand what you're trying to do but `=TRIM(RIGHT(SUBSTITUTE(A1,"-",REPT("-",100)),100))` gets the last bit of text, `=TRIM(LEFT(RIGHT(SUBSTITUTE(A1," -,REPT("-",40)),60),40))` gets the second last and if you increase the 60 by 80 to 120 you'll get the 3rd last, 200 the forth last etc. i don't know if that helps at all. – Gordon Mar 03 '17 at 16:15

2 Answers2

1

There's no reverse function. I see two ways:

  1. write small function is sctipt: reverse array
  2. use ugly huge formula

Huge formula sample

=JOIN("-",QUERY({ArrayFormula(ROW(INDIRECT("A1:A"&COUNTA(SPLIT(A1,"-"))))),TRANSPOSE(SPLIT(A1,"-"))},"select Col2 order by Col1 desc limit 2 "))

change limit 2 in the end of the formula to get N last elements.

Script sample

Try this:

function reverseLine(line) {
  return line[0].reverse();
}

Use as custom formula: =reverseLine(B1:D1) for line: aaa bbb ccc returns:

ccc
bbb
aaa
Max Makhrov
  • 17,309
  • 5
  • 55
  • 81
0

Provided the variable length is exclusively from the number of sets of the format -aaa appended to the first three characters then those three characters and as many further sets as desired may be stripped off the front with:

=replace(A1,1,find("-",A1)*B1,"")

where A1 contains the likes of aaa-bbb-ccc and B1 the number of sets additional to the first three characters to be removed, plus one.

pnuts
  • 58,317
  • 11
  • 87
  • 139