Not able to format text and autosums,

**stuartnoob** · 10-28-2010, 07:34 AM

Hi all,
I am having problems in formatting the cells after pulling/downloading data from a website.

The text i download should be in this format
-594-103
-543-966

No matter what i format the cells as (General) etc, the cells just calculate the sum and gives me this.
-697
-1509
But when i click on the actual cell, it shows the contents as
=-543-966
=-543-966
If i delete the "=", it still calculates..
Is there a way around this please?

p.s
If it helps, The code in the data-import is

WEB
1
http://website-address.php

Selection=AllTables
Formatting=rtf
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False

Thanks

**Marcol** · 10-28-2010, 09:32 AM

If there are only a few cases, manually precede the string with an apostrope

e.g.
'-543-966

Did you try Format > Text ?

**stuartnoob** · 10-28-2010, 10:02 AM

Originally Posted by Marcol

If there are only a few cases, manually precede the string with an apostrope

e.g.
'-543-966

Did you try Format > Text ?

Hi Marcol, Thanks for response.

Yea i tried Format "Text", there are several hundred instances so I highlighted the whole column, formatted it to "Text" and find & replaced "=" with apostophe ' ,
this actually worked for most cases.
But any of the 2 sets of 3 numbers that actually start with the number "0" ie
-087-789 or -312-023
lose the "0" so that i end up with -87-789 or -312-23

Stange huh

**Marcol** · 10-28-2010, 10:23 AM

Hmmm....?

I think the damage to these examples is already done at the import stage.

If you can import your web data to a text editor, then replace the - signs with an unique character, say #, import that file into excel as a text file.

Then use this
=SUBSTITUTE(A2,"#","-")

Copy and PasteSpecial > Values

This seems to work.

**stuartnoob** · 10-28-2010, 01:05 PM

Originally Posted by Marcol

Hmmm....?

I think the damage to these examples is already done at the import stage.

If you can import your web data to a text editor, then replace the - signs with an unique character, say #, import that file into excel as a text file.

Then use this
=SUBSTITUTE(A2,"#","-")

Copy and PasteSpecial > Values

This seems to work.

Hi,
Yea im trying to find a way to pull data in Text editor, automatic importing from excel is easy to use but as to do it via another way i havent a clue. I need a Word help forum LOL

I can physically copy n paste it from the site straight into Text editor, replace = with blank as you said then paste it into Excel.

Thanks for your help

Stu

**Marcol** · 10-28-2010, 01:40 PM

I don't think that will work.

Check Post #4 again.

I think doing as you suggest will just take you back to the start, it does if you use notepad whether you paste from notepad, or import as a .txt file.

I used this in a text file
#087#789
#312#023
#594#103
#543#966

then applied the substitute function

[EDIT]
Could you paste a typical table into word and post it?

**stuartnoob** · 10-28-2010, 04:12 PM

Got ya!

yes i see now, works! easier than before but im unable to change the import options.
I think its an importing software problem that adds the =- which screws it all up.

Thanks Marcol