Save number as text in txt format wo changing the format

[Tysken]

In order to export an excel table into another program I first need to save the excel file as .txt. The .txt file can then be imported by the other program.

First I however need to make excel understand that the value should be a text and not a value. I therefore format the number as text (0000150235) by adding "0000150235". After saving the file as .txt the format changes from "0000150235" to """0000150235""". I do however need the format in the .txt file to be "0000150235". Can anyone tell me how I can save "0000150235" as .txt and get the value "0000150235" in the .txt file.

Thank you

[pauluk]

simple way. Once you have saved it as text open it in notepad and use find and replace

Find: ""
Replace with: "

Thats an easy way

another way rather then using the "" to define it to excel that it is text is to format the cell.

Select the range and right click goto format cells, and in the tab number selecte the text category.

When the sheet is saved as a txt file then you will have the number with as
"0000150235"

[oldchippy]

You could also use the following formula

=TEXT(A2,"0000000000"), then copy and paste special > Values

[Tysken]

simple way. Once you have saved it as text open it in notepad and use find and replace

Find: ""
Replace with: "

Thats an easy way

another way rather then using the "" to define it to excel that it is text is to format the cell.

Select the range and right click goto format cells, and in the tab number selecte the text category.

When the sheet is saved as a txt file then you will have the number with as
"0000150235"

Thank you.

The "Find" and replace option is not applicable due size of the .txt file. It has more than 100.000 rows (using Excel 2007) and the Notepad dies on me when I try the "Find" and "Replace" option.

I have tried the other option (formatting the cell) and it only gives me 0000150235 in the .txt file and not "0000150235".

Sorry!

[pauluk]

rather then saving a s an ms-dos txt file save it as a formatted text(Space Delimited)
file extension .prn
open that .prn in notepad and save as a txt file that should work and keep the "

[Tysken]

rather then saving a s an ms-dos txt file save it as a formatted text(Space Delimited)
file extension .prn
open that .prn in notepad and save as a txt file that should work and keep the "

Thank you, it worked, but created a new problem. The tabs in the .txt file are replaced by space. This makes the data impossible to import into the other program.

[oldchippy]

OK, try this, with """0000150235""" in A1, put this in B1

=MID(A1,3,12), then copy and paste special as before

[Tysken]

OK, try this, with """0000150235""" in A1, put this in B1

=MID(A1,3,12), then copy and paste special as before

Thank you, unfortunately I still get """0000150235""" in the .txt file.

Cheers

[oldchippy]

In that case modify the formula to

=MID(A1,4,10) does that help?

[Tysken]

In that case modify the formula to

=MID(A1,4,10) does that help?

Your formula works brilliant in excel. The probelm first occur after I save the file as .txt.

Cheers

[oldchippy]

Before you saved it, did you copy and paste values only? format cell as text?

[Tysken]

Before you saved it, did you copy and paste values only? format cell as text?

Yes, I did.

[pauluk]

maybe this addin can help

Yoiu might need to find an addin or something.

Even when you have it as text and export it does not do it with "

http://www.asap-utilities.com/asap-u...ilities=Export

Its free ASAP i think

[Bryan Hessey]

Before you saved it, did you copy and paste values only? format cell as text?

HI oldchippy,

it's not the cell value at fault, the cell has "number" (or "0006767670" ) etc

it's when you export a file some fields are set in quotes, ie, for a CSV file any comma in the data requires that field to be set in quotes (to preserve the comma)
In this case the double-quotes is set in quotes, so that "000" becomes
" "" 000 "" " where the blue pair are the 'set-in' quotes, and the red 'double-double-quotes' signify this is NOT a blue set-in quotes.

The easy way is in Notepad to Replace all """ with " (ie, 3 double-quotes with one)

hth
---

[Tysken]

The easy way is in Notepad to Replace all """ with " (ie, 3 double-quotes with one)

hth---

I have tried that, but the file is too big. The Notepad dies on me (over 40.000 rows)

[Bryan Hessey]

I have tried that, but the file is too big. The Notepad dies on me (over 40.000 rows)

was Wordpad any better?

or, ignoring the 'seperated' in comma separated values, was Paul's addin any good?

---

[Tysken]

was Wordpad any better?

or, ignoring the 'seperated' in comma separated values, was Paul's addin any good?

---

Unfortunately not. The Wordpad was as slow and I had to cancel it after 30 minutes. Paul's addin seems to be great, I cannot however get it to work properly in Excel 2007....

Any more ideas?

Cheers

[pauluk]

Version 4.0 and upwards should work with excel 2007
make sure you have the lastest version which i assume you have as it was released in november 2006.

The only other suggestion i can make and it will be a long one is to the use the find and replace, but before running it split the txt file into mangable sections so that it does not freeze crash etc....

It all depends how automated you want the process. from the sounds of this it is you who is doing the bulk of the work, so it may not be problem at present but in the long you will need a more robust solution.

I know it is a headache but thats life....

[kghexce]

Just tried my favourite NOTEPAD replacement (NOTETAB) and it searched and replaced "" with " on 1,000,000 lines in under 2 seconds on my notebook.

Many versions from free to "pro" at
http://www.notetab.com/

Extremely useful Block Cut/Copy/Paste capability. One can take 'chunks' of text out of the middle of lines based on specified column starting position, ending position and number of rows. Thus I often use with Excel for modifying hyperlink formula's when making reference changes on thousands of rows...
CRTL ~, copy forumulas, modify in Notetab, recopy and paste into new location.

Has very handy scripting macros (albeit with a steep learning curve) when working with text.