split row of cells at semicolon

[lbeck]

Suppose I have a program that returns data in one row of cells. The cells in the row repeat in groups of five. I have the ability to format the exported data, and want to place a semicolon or other character at the end of each group of five to produce a line break.

My objective is to have five columns, each with the same data category, so that 12345;12345;12345 becomes:

12345
12345
12345

in five columns.

Each digit represents a cell, so the above example would no longer be 15 cells in one row but 3 rows of 5 cells each.

[NoSparks]

This is an interesting question, but I'm not exactly sure what it is.

If the aim is to insert semicolons (or other character) in the raw data from the "suppose I have a program", prior to Excel getting the data, it's probably a question that should be asked of that particular program.

If the aim is to accept the raw data as is and have Excel manipulate it before placement of the data into cells, we would need to have an accurate sample of the data Excel is getting to work with. I emphasize accurate because if the data is now going into separate cells then the 12345 as you indicate would have a delimiting character between the individual digits.

[lbeck]

Sorry, I was trying to be concise. The actual application is this. I have a plug-in to Photoshop Lightroom that exports EXIF data for each photograph selected from the library.The data is reported as a.TXT file. I can copy the TXT file and paste it onto an Excel spreadsheet. Problem is that The data is presented as a long string with multiple data sets, Each with identical, repeating fields. What I want to do is to separate that long row into columns.then I would put headings on each column for shutter speed, aperture etc. I was thinking that I could place a character like a ; after each repeating data set and have Excel parse that data from the row and place it into columns. I believe this is possible, but I don't know how to do it.

[NoSparks]

I think you're over thinking things and saying you want to alter the text file before giving it to Excel.

Personally, if the text file is copying into Excel now, I'd paste it into a sheet, run a macro with a loop or two to move the cell contents to the desired locations then delete the original.

Not seeing the actual text file or an Excel sheet with the text file already copied in, suggesting anything more would just be guessing.

[lbeck]

Macro......

That word scares me. I'm not an Excel expert and have never written a macro of any sort. But I have worked with most of the Excel menu functions over the years. I seem to remember cut/pasting text into a spreadsheet and a wizard appearing asking if I wanted the cells to be parsed at a character like a comma or semicolon, or whether I wanted fixed width (vertical lines appearing in the wizard) columns.

That's kind of what I was hoping. For example "Go to menu/Insert (or data, whatever)/a/b/c" to find how to split the horizontal row of text to split it at designated points and move the groupings into columns.

I'm still not convinced that there isn't a way for Excel to do the work without my need to write a unique macro. Maybe I'm wrong, but I'll keep trying.

Just for fun, I've pasted one set of exported data groups below with semicolons inserted at the desired break points. This can be bulk copied and pasted into cell A:1 and a long string appears. The original text had many spaces between data entries like the export expected data to be parsed from the string. my preview of this post shows that white space removed. I can of course cut the text groups from the string and align them into columns manually, but that would be more work than my objectives can tolerate.

I'm not trying to get you to do the work for me, only to give the best example. Once I get this figured out, I plan to export literally thousands of individual pieces of information to construct a database of information that I then can search and sort in Excel.


20060814-100_3700132920.JPG 1/500 ISO 100 KODAK DX7630 ZOOM DIGITAL CAMERA Microsoft Photo Gallery 16.4.3528.331 ;20060814-3702 enhanced132950.JPG 1/500 ISO 100 KODAK DX7630 ZOOM DIGITAL CAMERA Microsoft Photo Gallery 16.4.3528.331 ;20060814-100_3704 (2)133014.jpg 1/750 ISO 100 KODAK DX7630 ZOOM DIGITAL CAMERA Microsoft Photo Gallery 16.4.3528.331 ;20060814-100_3704133014.JPG 1/750 ISO 100 KODAK DX7630 ZOOM DIGITAL CAMERA ;20060814-100_3705133045.JPG 1/500 ISO 100 KODAK DX7630 ZOOM DIGITAL CAMERA Microsoft Photo Gallery 16.4.3528.331 ;20060814-100_3707134901.JPG 1/500 ISO 100 KODAK DX7630 ZOOM DIGITAL CAMERA ;20060814-100_3717140231.JPG 1/500 ISO 100 KODAK DX7630 ZOOM DIGITAL CAMERA ;20060814-100_3733 Edited by Andy155547.jpg 1/1000 ISO 100 KODAK DX7630 ZOOM DIGITAL CAMERA Adobe Photoshop Lightroom 5.7 (Windows) ;20060814-100_3733155547.JPG 1/1000 ISO 100 KODAK DX7630 ZOOM DIGITAL CAMERA Microsoft Photo Gallery 16.4.3528.331 ;20060814-100_3733 Edited by Lee155547.jpg 1/1000 ISO 100 KODAK DX7630 ZOOM DIGITAL CAMERA Microsoft Photo Gallery 16.4.3528.331 ;20060816-Waterfall Cascades135737.jpg 1/500 ISO 100 KODAK DX7630 ZOOM DIGITAL CAMERA ;20060816-Snow-covered Mountains - cloned140319.jpg 1/750 ISO 100 KODAK DX7630 ZOOM DIGITAL CAMERA ;20060816-Snow-covered Mountains140319.jpg 1/750 ISO 100 KODAK DX7630 ZOOM DIGITAL CAMERA ;20060822-Panorama at Lak Louise (2)154143.jpg Microsoft Photo Gallery 16.4.3528.331 ;20060822-Panorama at Lak Louise154143.jpg ;20060822-Pan-Emerald Lake155414.jpg ;20060822-Pan-Gondola191518.jpg ;IMG_0002.JPG 3.94 1/40 ISO 64 iPhone 5c Microsoft Windows Live Photo Gallery 15.4.3555.308 ;IMG_0004.JPG ;IMG_0008.JPG 6.08 1/120 ISO 50 iPhone 5c Microsoft Windows Live Photo Gallery 15.4.3555.308 ;IMG_0009.JPG 6.58 1/150 ISO 50 iPhone 5c Microsoft Windows Live Photo Gallery 15.4.3555.308 ;IMG_0010.JPG 6.74 1/150 ISO 50 iPhone 5c Microsoft Windows Live Photo Gallery 15.4.3555.308 ;IMG_0011.JPG 4.97 1/120 ISO 80 iPhone 5c Microsoft Windows Live Photo Gallery 15.4.3555.308 ;

[lbeck]

Just for kicks, I'm attaching a copy of the spreadsheet with the string inserted.

[NoSparks]

Just for more kicks can you attach the actual file you used for what is in row one of the Excel sheet. My suspicion is that it's full of unprintable characters which don't show with what you pasted into #5.

The semicolons in your first row are a consistent 9 columns apart. Did the blank columns happen on their own? If so, use replace or substitute to change those semicolons to chr(10) and give it a try.

[NoSparks]

Have a look at this, there's a macro included to take your first row and put it into individual rows starting on row 10.
Alt + F8 will bring up the macro dialogue so you can run it.

I would hope this isn't necessary and that the LR add-in would have some kind of setup that allows the selection of delimiters.

[lbeck]

Wow! That works.

Like you, I would think that the guy who coded the Plugin has the expertise to format to columns rather than one long string.

I need to learn more about macros. In the mean time, I'll use yours and see if it will work for the entire dataset of thousands of entries. If not, maybe I can become smart enough to modify the macro.

Thanks for your help.