Number stored as text when copying data from one sheet to another

[Ad Cl]

I have a report with data in it - I need to copy that into a different spreadsheet, but when I copy and paste values - I get number stored as text error in the new spreadsheet which is a pain.

The cells in the original sheet - where I am copying it from - are based on various formulas:

=IF('INPUT - RP EXPORT'!E1281="API","0","1")

=IF(OR(I1281=" 0.00",I1281=""),"0","1")

=REPLACE('INPUT - RP EXPORT'!J1281,1,5,"")

=MID(INDEX('INPUT - RP EXPORT'!1281:1281,MATCH("*Bespoke Tracking*",'INPUT - RP EXPORT'!1281:1281,0)),FIND(":",INDEX('INPUT - RP EXPORT'!1281:1281,MATCH("*Bespoke Tracking*",'INPUT - RP EXPORT'!1281:1281,0)))+2,50)

How do I make it so when I paste them into the new spreadsheet I don't get this error?

[AliGW]

I presume that they are showing in the original worksheet as general or text?

One thing you need to do is change this:

=IF('INPUT - RP EXPORT'!E1281="API","0","1")

to this:

=IF('INPUT - RP EXPORT'!E1281="API",0,1)

so that the 0 or 1 are returned as numbers, not text.

This one:

=MID(INDEX('INPUT - RP EXPORT'!1281:1281,MATCH("*Bespoke Tracking*",'INPUT - RP EXPORT'!1281:1281,0)),FIND(":",INDEX('INPUT - RP EXPORT'!1281:1281,MATCH("*Bespoke Tracking*",'INPUT - RP EXPORT'!1281:1281,0)))+2,50)

can probably be converted to a number like this:

=--MID(INDEX('INPUT - RP EXPORT'!1281:1281,MATCH("*Bespoke Tracking*",'INPUT - RP EXPORT'!1281:1281,0)),FIND(":",INDEX('INPUT - RP EXPORT'!1281:1281,MATCH("*Bespoke Tracking*",'INPUT - RP EXPORT'!1281:1281,0)))+2,50)

I cannot immediately see why this one would be problematic:

=REPLACE('INPUT - RP EXPORT'!J1281,1,5,"")

but again, you could try this:

=--REPLACE('INPUT - RP EXPORT'!J1281,1,5,"")

[Ad Cl]

Great tips - thanks, solved the issue!

[AliGW]

Good to know.