Avatar of BDManagedServices
BDManagedServicesFlag for United States of America asked on

Import charts with spaces instead of tabs into excel

I have 1000s of charts that were created (not by me) and emailed to me. I would like to import them into excel, but the chart was typed out in straight text with spaces where any normal person would use tabs. Here's an example:

Date        Name           Document
12/1       John Doe       report1.doc
12/2       Jane Smith     form1.doc

How can I import these cleanly with minimal formatting needed?
SpreadsheetsMicrosoft Excel

Avatar of undefined
Last Comment
Bruce Cadiz

8/22/2022 - Mon
SiddharthRout

For a second I was confused when you said charts :)

You can open them from excel and then use text to columns to separate them into relevant cells.

My suggestion would be to write a macro which automates this entire operation since 1000 files are involved...

Sid
TommySzalapski

You could use text-to-columns like Siddharth said
Data->Text To Columns
Choose 'Delimited'
Select 'Space'
Mark "Treat consecutive delimiters as one"

But since there are spaces in the actual data this might not work right.
If you open the data in Word and do a find replace and replace "   " with "  " (three spaces with one space) then do this over and over until it can't find any more, then all your data will be separated by a double space, then you can replace the "  " (two spaces) with ^t (the code for tab character) then it will paste cleanly into Excel.
theGove

It looks like the spaces have been used to put data into columns.  Can you use the text import wizard (Tab:data, Group:Get External Data,Button:From Text) with the default option (Fixed width) to import the data?  As long as there is a continuous vertical column of white space between your data fields, it should do the trick.
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER CERTIFIED SOLUTION
Bruce Cadiz

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question