Excel formatting is annoying!
- DEnc
- Articles: 0
- Posts: 287
- Joined: Tue Aug 13, 2019 11:52 pm
- 4
- Location: Northern CA
- Status:
Offline
-
TSS Photo of the Day
Excel formatting is annoying!
My current version is Office 2016. I’ve had luck in turning off this feature in earlier versions of Excel with File/Options/Advanced/TransitionFormulaEvaluation, but with the current version, it’s a no go.
With other text files I haven’t delimited the file for import; I extracted columns in Excel with the left(text)/right(text) commands. Even then it’s a slog to fight the formatting, but ultimately it’s doable. However this file’s rows are particularly busy--with space delimiters of varying lengths across 11 columns of data.
Is there anyone out there that’s successfully battled through this nonsense? Is there some setting that finally and definitively turns formatting features off? Any advice would be appreciated, else I’ll resort to cracking open SQL For Dummies!
Thanks,
David
- JimMinCT
- Articles: 0
- Posts: 393
- Joined: Tue May 07, 2019 10:24 pm
- 5
- Location: Ct
- Status:
Offline
-
TSS Awards Badges
TSS Photo of the Day
Re: Excel formatting is annoying!
Not sure if this is possible over the entire document you're importing. Sorry if not enough help. Yeah, Excel is no fun...
- DEnc
- Articles: 0
- Posts: 287
- Joined: Tue Aug 13, 2019 11:52 pm
- 4
- Location: Northern CA
- Status:
Offline
-
TSS Photo of the Day
Re: Excel formatting is annoying!
- JimMinCT
- Articles: 0
- Posts: 393
- Joined: Tue May 07, 2019 10:24 pm
- 5
- Location: Ct
- Status:
Offline
-
TSS Awards Badges
TSS Photo of the Day
Re: Excel formatting is annoying!
My excel skills are probably about a 6 on a 1-10 scale, but I get by.
I've written a couple that ended up saving me a ton of time.
- DEnc
- Articles: 0
- Posts: 287
- Joined: Tue Aug 13, 2019 11:52 pm
- 4
- Location: Northern CA
- Status:
Offline
-
TSS Photo of the Day
Re: Excel formatting is annoying!
- Graeme1858
- Co-Administrator
- Articles: 1
- Posts: 7643
- Joined: Mon Jun 24, 2019 7:16 pm
- 4
- Location: North Kent, UK
- Status:
Offline
-
TSS Awards Badges
TSS Photo of the Day
I Broke The Forum.
Re: Excel formatting is annoying!
Any chance you can post a Dropbox or Google Drive etc link to your text file?
It might be easier to sort out and then explain if the data was on the screen in front of me.
I'm too Excel rusty to visualize it otherwise!
Regards
Graeme
Celestron 9.25 f10 SCT, f6.3FR, CGX. Sky-Watcher Evostar-120, HEQ5
ASI1600MM Pro, ASI294MC Pro, ASI224MC
ZWO EFW, ZWO OAG, ASI220MM Mini.
APM 11x70 ED APO Binoculars.
https://www.averywayobservatory.co.uk/
- DEnc
- Articles: 0
- Posts: 287
- Joined: Tue Aug 13, 2019 11:52 pm
- 4
- Location: Northern CA
- Status:
Offline
-
TSS Photo of the Day
Re: Excel formatting is annoying!
I've instituted Plan B just in case; my SQL primer will arrive tomorrow!
David
https://www.dropbox.com/s/24j15aohufie7 ... 1.txt?dl=0
- ARock
- Articles: 0
- Posts: 363
- Joined: Mon May 13, 2019 4:06 am
- 5
- Location: USA
- Status:
Offline
-
TSS Awards Badges
Re: Excel formatting is annoying!
https://www.cloudynights.com/topic/4448 ... sv-format/
the last post has a link to a github page which has an excel spreadsheet (maybe not the most current version of WDS)
https://github.com/miguelvb/wds_catalog
Scopes: Zhumell Z8, Meade Adventure 80mm, Bushnell 1300x100 Goto Mak.
Mount: ES EXOS Nano EQ Mount, DIY Arduino+Stepper drives.
AP: 50mm guidescope, AR0130 based guidecam, Canon T3i, UHC filter.
EPs: ES82 18,11,6.7mm, Zhumell 30,9mm FJ Ortho 9mm, assorted plossls, Meade 2x S-F Barlow, DGM NPB filter.
Binos: Celestron Skymaster 15x70 (Albott tripod/monopod), Nikon Naturalist 7x35.
- Ylem Online
- Articles: 0
- Posts: 7653
- Joined: Sun May 12, 2019 2:54 am
- 5
- Location: Ocean County, New Jersey
- Status:
Online
-
TSS Photo of the Day
Re: Excel formatting is annoying!
-Jeff
Member; ASTRA-NJ
Orion 80ED
Celestron C5, 6SE, Celestar 8
Vixen Porta Mount ll
Coronado PST
A big box of Plossls
Little box of filters
- DEnc
- Articles: 0
- Posts: 287
- Joined: Tue Aug 13, 2019 11:52 pm
- 4
- Location: Northern CA
- Status:
Offline
-
TSS Photo of the Day
- DEnc
- Articles: 0
- Posts: 287
- Joined: Tue Aug 13, 2019 11:52 pm
- 4
- Location: Northern CA
- Status:
Offline
-
TSS Photo of the Day
Re: Excel formatting is annoying!
Thank you for these! I got the Excel file, looks great. Also the links look helpful with regard to ideas for SQL syntax once I have SQL up and running--I'll nab the latest catalog if all goes well.ARock wrote: ↑Fri May 08, 2020 12:21 am From
https://www.cloudynights.com/topic/4448 ... sv-format/
the last post has a link to a github page which has an excel spreadsheet (maybe not the most current version of WDS)
https://github.com/miguelvb/wds_catalog
- Graeme1858
- Co-Administrator
- Articles: 1
- Posts: 7643
- Joined: Mon Jun 24, 2019 7:16 pm
- 4
- Location: North Kent, UK
- Status:
Offline
-
TSS Awards Badges
TSS Photo of the Day
I Broke The Forum.
Re: Excel formatting is annoying!
Nailed it!
In Excel
Click File, Open, Browse
Change file type to all files
Open your text file
Text Import Wizard starts
Original data type - Fixed width
Click Next
On Step 2 of 3 Create break lines to the left of the two
Click Next
Click Finish
If that didn't make sense, here's an Excel file:
https://1drv.ms/x/s!AtzUKEWck9gpvtI1qFi ... g?e=j0v2jk
I love Excel!
Regards
Graeme
Celestron 9.25 f10 SCT, f6.3FR, CGX. Sky-Watcher Evostar-120, HEQ5
ASI1600MM Pro, ASI294MC Pro, ASI224MC
ZWO EFW, ZWO OAG, ASI220MM Mini.
APM 11x70 ED APO Binoculars.
https://www.averywayobservatory.co.uk/
- DEnc
- Articles: 0
- Posts: 287
- Joined: Tue Aug 13, 2019 11:52 pm
- 4
- Location: Northern CA
- Status:
Offline
-
TSS Photo of the Day
- Graeme1858
- Co-Administrator
- Articles: 1
- Posts: 7643
- Joined: Mon Jun 24, 2019 7:16 pm
- 4
- Location: North Kent, UK
- Status:
Offline
-
TSS Awards Badges
TSS Photo of the Day
I Broke The Forum.
Re: Excel formatting is annoying!
Celestron 9.25 f10 SCT, f6.3FR, CGX. Sky-Watcher Evostar-120, HEQ5
ASI1600MM Pro, ASI294MC Pro, ASI224MC
ZWO EFW, ZWO OAG, ASI220MM Mini.
APM 11x70 ED APO Binoculars.
https://www.averywayobservatory.co.uk/
- Graeme1858
- Co-Administrator
- Articles: 1
- Posts: 7643
- Joined: Mon Jun 24, 2019 7:16 pm
- 4
- Location: North Kent, UK
- Status:
Offline
-
TSS Awards Badges
TSS Photo of the Day
I Broke The Forum.
Re: Excel formatting is annoying!
Celestron 9.25 f10 SCT, f6.3FR, CGX. Sky-Watcher Evostar-120, HEQ5
ASI1600MM Pro, ASI294MC Pro, ASI224MC
ZWO EFW, ZWO OAG, ASI220MM Mini.
APM 11x70 ED APO Binoculars.
https://www.averywayobservatory.co.uk/
- DEnc
- Articles: 0
- Posts: 287
- Joined: Tue Aug 13, 2019 11:52 pm
- 4
- Location: Northern CA
- Status:
Offline
-
TSS Photo of the Day
Re: Excel formatting is annoying!
- DEnc
- Articles: 0
- Posts: 287
- Joined: Tue Aug 13, 2019 11:52 pm
- 4
- Location: Northern CA
- Status:
Offline
-
TSS Photo of the Day
Re: Excel formatting is annoying!
That was easy! I glossed over the part of the wizard that allows you to manually create column breaks. Usually I work with the delimited file type, but this is much easier.
Thanks again Graeme.
David
Create an account or sign in to join the discussion
You need to be a member in order to post a reply
Create an account
Not a member? register to join our community
Members can start their own topics & subscribe to topics
It’s free and only takes a minute