Note that regex is much easier in a text editor than in LO Calc. I do this kind of thing as normal course of my activity: get the file to look like the .csv i want with an editor first, then import it into LO Calc. Tab delimited is the least error prone... (you can make a tab in most editors with regex with \t )
I use jEdit because it's ability to record the search/replace activity, then allow you to edit and replay the recorded "macros" on other files helps me work far far faster. But any text editor with regex functionality will eventually have to moving faster than LO Calc. But it's a steep learning curve. On Sun, Mar 13, 2022 at 9:18 AM Rogier F. van Vlissingen <vlisc...@gmail.com> wrote: > Awesome, thanks! > > A bit puzzling... I thought I exactly copied your previous instructions, > but I got the idiot results you saw. Now I downloaded your spreadsheet, and > indeed it came out better in your case, so there must be some subtle > mistake some place. I will have to repeat the process and figure that out. > > Thanks so much. > > On Sun, Mar 13, 2022 at 10:00 AM Michael D. Setzer II <msetze...@gmail.com > > > wrote: > > > Not clear on how you got that break down with my instructions I get A > thru > > AB and here I transposed them to show. PM2.5 is in Column G not two. > > Then had formula to convert date and time value. Moved > > A > > Date > > B > > 20220302 > > 03/02/22 > > “=DATE(LEFT(AH2,4),MID(AH2,5,2),RIGHT(AH2,2)) > > C > > Time > > D > > 1631 > > 04:31:00 PM > > “=TIME(INT(AH4/100),MOD(AH4,100),0) > > E > > PM1.0 > > F > > 0 > > G > > PM2.5 > > H > > 0 > > I > > PM10 > > J > > 0 > > K > > CO2 > > L > > 10 > > M > > HCHO > > N > > 0.001 > > O > > TVOC > > P > > 0 > > Q > > >0.3DustNum > > R > > 0 > > S > > >0.5DustNum > > T > > 0 > > U > > >1.0DustNum > > V > > 0 > > W > > >2.5DustNum > > X > > 0 > > Y > > >5.0DustNum > > Z > > 0 > > AA > > >10DustNum > > AB > > 0 > > On 13 Mar 2022 at 9:30, Rogier F. van Vlissingen wrote: > > From: "Rogier F. van Vlissingen" <vlisc...@gmail.com> > > Date sent: Sun, 13 Mar 2022 09:30:49 -0400 > > Subject: Re: [libreoffice-users] Spreadsheet question > > To: "Michael D. Setzer II" <msetze...@gmail.com> > > Copies to: users@global.libreoffice.org > > > Funny complication. > > > > > > One of the variable labels is PM2.5, but in this case that became two > > > columns, H, and I respectively, like this: > > > > > > H I > > > PM2 5 > > > > > > > > > On Sun, Mar 13, 2022 at 9:27 AM Rogier F. van Vlissingen < > > vlisc...@gmail.com> > > > wrote: > > > > > > > Awesome, thanks! > > > > > > > > I replicated that, and now I can just name the columns and the delete > > the > > > > preceding columns with the variable names and I am in business. > > > > > > > > One of these babies is 11775 rows ;-) > > > > > > > > > > > > > > > > On Sun, Mar 13, 2022 at 9:18 AM Michael D. Setzer II < > > msetze...@gmail.com> > > > > wrote: > > > > > > > >> Loaded the csv file with libreoffice and click on space and > > > >> other boxes and entered :. > > > >> That brings in columns A thru AB. > > > >> > > > >> > > > >> On 13 Mar 2022 at 9:05, Rogier F. van Vlissingen wrote: > > > >> > > > >> From: "Rogier F. van Vlissingen" > > > >> <vlisc...@gmail.com> > > > >> Date sent: Sun, 13 Mar 2022 09:05:56 -0400 > > > >> Subject: Re: [libreoffice-users] Spreadsheet > > > >> question > > > >> To: David Lynch <dlynch131...@gmail.com> > > > >> Copies to: users@global.libreoffice.org > > > >> > > > >> > wow... I had never used that function before... > > > >> > > > > > >> > > I am not quite there yet, however, but at least this is a path. > > Here > > > >> is an > > > >> > > example of one of the strings > > > >> > > > > > >> > > Date:20220302 Time:1631 PM1.0:000 PM2.5:000 PM10:000 CO2:0010 > > > >> HCHO:0.001 > > > >> > > TVOC:0.000 >0.3DustNum:00000 >0.5DustNum:00000 >1.0DustNum:00000 > > > >> > > >2.5DustNum:00000 >5.0DustNum:00000 >10DustNum:00000 > > > >> > > > > > >> > > Using the expression as you wrote it, I get fields, like this: > > > >> > > Date 20220302 Time 1631 PM1.0 000 PM2.5 000 PM10 000 CO2 0010 > HCHO > > > >> 0.001 > > > >> > > TVOC 0.000 >0.3DustNum 00000 >0.5DustNum 00000 >1.0DustNum 00000 > > > >> > > >2.5DustNum 00000 >5.0DustNum 00000 >10DustNum 00000 and then I > > could > > > >> > > name the columns and delete the columns with the variable names. > > > >> > > > > > >> > > I will have to study that function... thanks. > > > >> > > > > > >> > > > > >> > > > > >> > > > > >> > > On Sun, Mar 13, 2022 at 8:41 AM David Lynch < > > dlynch131...@gmail.com> > > > >> > > wrote: > > > >> > > > > > >> > >> On 13/03/2022 12:04, Rogier F. van Vlissingen wrote: > > > >> > >> > Var1: value1 Var2: value2 Var3: value 3 ... Var10: value10 > > > >> > >> > > > >> > >> Place the strings in column 1 than put > > > >> > >> > > > >> > >> =REGEX($A1,"(?<=^| |:)[^ ]+?(?=:| |$)",,COLUMN()-1) > > > >> > >> > > > >> > >> in column 2 and drag right for ten columns and down. > > > >> > >> > > > >> > >> David Lynch > > > >> > >> > > > >> > >> > > > >> > > > > >> > -- > > > >> > To unsubscribe e-mail to: > users+unsubscr...@global.libreoffice.org > > > >> > Problems? > > > >> > > https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ > > > >> > Posting guidelines + more: > > > >> https://wiki.documentfoundation.org/Netiquette > > > >> > List archive: https://listarchives.libreoffice.org/global/users/ > > > >> > Privacy Policy: https://www.documentfoundation.org/privacy > > > >> > > > >> > > > >> +------------------------------------------------------------+ > > > >> Michael D. Setzer II - Computer Science Instructor > > > >> (Retired) > > > >> mailto:mi...@guam.net > > > >> mailto:msetze...@gmail.com > > > >> Guam - Where America's Day Begins > > > >> G4L Disk Imaging Project maintainer > > > >> http://sourceforge.net/projects/g4l/ > > > >> +------------------------------------------------------------+ > > > >> > > > >> > > > >> > > > >> The following section of this message contains a file attachment > > > >> prepared for transmission using the Internet MIME message format. > > > >> If you are using Pegasus Mail, or any other MIME-compliant system, > > > >> you should be able to save it or view it from within your mailer. > > > >> If you cannot, please ask your system administrator for assistance. > > > >> > > > >> ---- File information ----------- > > > >> File: BRSmart128S_0302-0313_2022_RFvV_BR.ods > > > >> Date: 13 Mar 2022, 23:16 > > > >> Size: 675913 bytes. > > > >> Type: Libre-sheet > > > >> > > > > > > > > > > -- > > > To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org > > > Problems? > > https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ > > > Posting guidelines + more: > > https://wiki.documentfoundation.org/Netiquette > > > List archive: https://listarchives.libreoffice.org/global/users/ > > > Privacy Policy: https://www.documentfoundation.org/privacy > > > > -- > To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org > Problems? > https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ > Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette > List archive: https://listarchives.libreoffice.org/global/users/ > Privacy Policy: https://www.documentfoundation.org/privacy > -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
