Forcing Excel to interpret number as text

I like HOTPEPPER's use of FIND in "Find a Hyphen and return the rest " http://www.mrexcel.com/board2/viewtopic.php?t=130206 but alas it "finds" the negative in -2, i.e. FIND returns 1, and not 2. It's too late to prevent the subtraction.

What do you want to see or get? 4 and 6 ? Is there always single numbers? You want to see X46 maybe? Do you want the absolute value of the equation(=ABS(4-6) ?

Need more info,
Michael

How is the =4-6 being imported into A1?

Is it being copied from somewhere within Excel? (ie...)
Sheets("Sheet1").[A1].Value = "'" & Sheets("Sheet2").[Z100].Formula

Dan

If by "imported" you mean you are getting the data from an externald ata source, then at the time of the import specify that the column contains text.

Changing the format after the fact is too late. It must be changed before importing the data.

Gates Is Antichrist said:

Imported cell A1 is
=4-6
I cannot figure out how to stop Excel from performing the math. I want to parse out the 4 and 6, but Excel changes it to minus 2.

C1 ="X" & A1
gives X-2

Formatting A1 as text doesn't change anything.

{snip}

Click to expand...

1) Select the range in which you want to extract the formula from
2) With the source range selected, press Ctrl H to open the find and replace dialog box
3) Replace = with '=

Note that if the formula result is being referenced elsewhere as an actual value you'll have to copy the formula someplace else before doing the above.

Regards,

Alex

Tusharm is right. Import it by selecting text in the column data format option (Data, get external data,import text file,next,next)

Almost all great replies and thanks; one solution appears to have succeeded - but tentatively.

Try this test:
format a new sheet's cells A1 to C3 as text;
scrape-copy only the 3 characters 4-6 on this line in this message with Ctl-C;
Ctl-V to A1

Sadly, it's already a date - April 6, 2005, and consistent with what everyone's said, "it's too late now." Everything fails.

Neonangel's approach looked like it had the best chance, but it failed on the above test, not finding either an equal sign OR a minus sign to replace. Another attempt had the paste destination left in its original general format; then replacing the minus sign with the letter Q responded "invalid" (it refused to make the cell be 4Q6). However replacing the equal sign with Q WORKED.

But not in my test scenario above!! I had to paste the entire line or lines from the web page that the source data is in. Unfortunately I can not provide the exact URL for anyone to try it; I hope that you have enough clues to resolve this.

One more piece of info that might be crucial: the page that I am pasting from has tables. I am clueless about web data. I do know that after pasting the rectangular set of data, and pasting to a new Excel page, that TABLE and TABLE_2 are produced.

I apologize for not being able to provide the URL but appreciate your help if you still can.

I found a test page that somewhat demonstrates the problem:
http://mlb.mlb.com/NASApp/mlb/mlb/standings/index.jsp

Scrape and ctl-C the 5 line rectangle, starting your scrape with the left edge of the letter N in New York, through the end of the Toronto line. Paste into a virgin worksheet (general format). You'll see that some cells became dates, and it seems that the original data is unreachable. And hmmm, yes, TABLE AND TABLE_1 are produced.

Now do the same thing but format A1 to L11 as text first in a new sheet. There's still a May 13 that seems hopeless. This time, though, TABLE, TABLE_2, TABLE_3, and TABLE_4 are produced.

Format the cell as text then copy paste the data from the website then choose pastespecial text