Help with Excel..

I

iDenTiTy

Expert Member
Joined
Apr 14, 2007
Messages
3,899
Reaction score
2
Location
Fourways (vicinity)
Hi guys,

I desperately need your assistance with this;

I have an excel file with the following:

1 | House | Dogs | x
2 | Cottage | Cats |

My problem is, that I need the above to look like this:

"1", "House", "Dogs", "x"
"2", "Cottage", "Cats", " "

The excel file has 14640 rows, so naturally typing them all out is not exactly "fun"..

Does anyone have an idea how I could get the above formatting done? (without manually typing out all 14k rows)

:confused:
 
Use "Text to columns" function. Are you using Excel 2007?
 
Select the entire column
Under menu DATA
select TEXT TO COLUMN
choose your method to split
Go
 
choose your split method "DELIMITED"
next
select delimiters.... yours looks like a "|" (shift & backslash) so select "OTHER" and type the delimiter into the white box
NEXT
If done correctly it will show solid lines where the column will start/finish.
If correct select FINISH.
 
If still not working, PM me for my email address, send file and I'll do it with you!
 
The data is currently in columns ?

You want a text file with the data seperated with commas, each value wrapped in double quotes ?

Export as CSV. Not sure how you will force excel to use the " as text qualifier.

(Ignore if I'm misunderstandig your question.)
 
Can insert a column with " in it and then concatenate the cells....
 
moggie said:
If still not working, PM me for my email address, send file and I'll do it with you!
Click to expand...

Thanks moggie, but the data is kinda sensitive..

:)

@ KobusDJ:

I exported as csv, but there are large spaces between text.
I opened it with Notepad++, but I now have to replace spaces with commas..

:(
 
Once you import the csv, all data should be in 1 column.

If your text is a single word (per column) you delete the multiple spaces between text by doing a FIND & REPLACE...

Select the whole column
Keyboard: CTRL-H
Find what: (double tap the spacebar)
Replace with: (single spacebar)

Repeat till there is a single space between text....

There might be a shorter way but I taught myself!
 
Seems like there is not an option in excel to add the " as text qualifier when you export.
 
I did a quick test.....

Import the excel sheet into Access. Make sure all fields are of type 'text'.

Export table to text file. Access let you choose the delimiter and text qualifier.
 
Last edited:
=""""&A1&""","""&B1&""","""&C1&""","""&D1&""""

Assuming columns are A, B, C and D... put this formula in E or higher...
 
Thanks guys,

Got it working.

I used Access to import the .xls and then exported it to a txt.

Access converted the lines into strings (" ").

Sweet.

Again, thanks to everyone that tried to assist me.

:)
 
Oh, and if you need a space if the fourth column is empty, replace the &D1& with &IF(ISBLANK(D1)," ",D1)&

Please provide a postal address for the invoice... ;)
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X