It looks like you're using an Ad Blocker.

Please white-list or disable AboveTopSecret.com in your ad-blocking tool.

Thank you.

 

Some features of ATS will be disabled while you continue to use an ad-blocker.

 

Help ATS via PayPal:
learn more

Help! Is anyone really good with Google Spreadsheets/writing scripts?

page: 1
2

log in

join
share:

posted on Oct, 25 2016 @ 09:58 PM
link   
Hello!!! I'm finishing a huge project and I really need your help!

I'm working on a large spreadsheet in Google Spreadsheets and I have multiple columns that contain cells with lists separated by semicolons--I want to split by semicolon delimiters, while maintaining the surrounding values. I've tried a few scripts (listed below) but I must be doing something wrong with all of them
I need a solution relatively fast so any help is really appreciated. I'm trying to get my spreadsheet ready for pivot tables

Basically I'm trying to go from this:

Study 1 | 2010 | Gorilla | Feeding; Resting | Adults
Study 2 | 2003 | Impala | Foraging | Adults; Juveniles

to

Study 1 | 2010 | Gorilla | Feeding | Adults
Study 1 | 2010 | Gorilla | Resting | Adults
Study 2 | 2003 | Impala | Foraging | Adults
Study 2 | 2003 | Impala | Foraging | Juveniles

I tried this script, which does exactly what I need (just a different delimiter), and received an error: Invalid return. (line 17, file "Code") webapps.stackexchange.com...

Then I tried this script but I wasn't able to modify it to point to the columns that I need to be split (/multiple columns): mashe.hawksey.info...

Then I tried this: Split single rows into multiple rows depending on whether one cell has values seperated by commas or new lines - Google spreadsheet and got this error: Unterminated regular expression literal. I tried the second formula on that page and got this error: Script function not found: extract

I can actually run it for the entire sheet (split all semicolons in all columns) if that's easier. The range of my sheet is A1:BT223. The specific columns that contain values that need to be split are N-W, AL, BB, BD, and BH.

I'm extraordinarily helpless with these things (I obviously don't know what I'm doing). I'm just really having trouble getting this to work and I'm sure for someone experienced in this these things it's a piece of cake.

Thank you so much!!! If you can help me I'll be so very grateful!!!




posted on Oct, 26 2016 @ 11:12 AM
link   
I can't help with your issue but - GOSH! it's good to see you around.



posted on Oct, 26 2016 @ 01:04 PM
link   
a reply to: kosmicjack

Haha, aww, thank you!!! Hopefully once this enormous headache of a thesis is done I'll actually have time to poke around more than just sassy political posts at 3am



posted on Oct, 26 2016 @ 07:15 PM
link   
a reply to: ravenshadow13

I need to see a sample.

Sounds to me like your simplest solution is to use the Text To Columns feature and use Colon as your delimiter. But without lookig, i can't say anythig for sure.



posted on Oct, 26 2016 @ 07:16 PM
link   
can you post a link to download the spreadsheet?

Can I open it in excel? Im extremely proficient with excel.



posted on Oct, 26 2016 @ 07:22 PM
link   
a reply to: bigfatfurrytexan

Text to columns won't work, because what I need is the delimited text to form new rows, not new columns. I'm generally very good with excel, as well, but I believe this requires a script.



posted on Oct, 26 2016 @ 07:29 PM
link   
a reply to: ravenshadow13

how about this as a cheap trick:

- copy the rows you want to delimit
- in a new sheet, right click and paste special-transpose

run your text to columns on that worksheet, then paste special-transpose back into the prior format. I use this trick to sort out data based on rows (since you can't autofilter rows).

Second option: can you run VB scripts in the google sheets tool?



posted on Oct, 26 2016 @ 07:33 PM
link   
a reply to: bigfatfurrytexan

I'm not sure how to work the transpose in this situation since I'm trying to preserve the content of the surrounding cells. Will it let me do that?

I guess the Google Sheets coding is based off of _javascript and not VB but I guess you can convert: stackoverflow.com...

If you see the above scripts that I linked to in the entry post, I think that's essentially what I need to do, but I can't get it to work... if you think you can help let me know and I'll PM you a link to a sample sheet



posted on Oct, 27 2016 @ 02:28 AM
link   
a reply to: ravenshadow13

Not a clue. I hate spreadsheets.

Wanted to say hello and good to see you



posted on Oct, 27 2016 @ 10:12 AM
link   
a reply to: Kandinsky

Aww thank you!!!
So glad to see that you're all still around







 
2

log in

join