# Forcing Excel to interpret number as text

#### Gates Is Antichrist

##### Well-known Member
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.

Formatting C1 as text causes it to look like
="X" & A1
(that's actually what's displayed - it even treats the equal sign as text).
This still doesn't give me the 4 and 6 to work with.

Other things I've tried all perform the subtraction. Even MID(A1,1,1) returns "-" - CHEEEEZ!!

Perhaps this is related to the old dilemma of trying to preserve trailing zeroes?

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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) ?

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}

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

Replies
19
Views
159
Replies
5
Views
575
Replies
4
Views
1K
Replies
1
Views
96
Replies
0
Views
886

1,203,430
Messages
6,055,337
Members
444,781
Latest member
rishivar

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back