trim values?

[daveyc18]

when i copy and paste numbers from an email,

i'm getting a bunch of spaces in front of the number, behind the number

the trim function doesnt work since it's for text...any way to get rid of spaces for values?

 

[rabsofty]

are you sure they are spaces and not tabs

 

[Sufiyan97]

Can you post some rows of your data using xl2bb

Try

=SUBSTITUTE(A2," ","")+0

 

[daveyc18]

when i copy and paste numbers from an email,

i'm getting a bunch of spaces in front of the number, behind the number

the trim function doesnt work since it's for text...any way to get rid of spaces for values?

are you sure they are spaces and not tabs

sometimes text to column using spaces works ...and doing this cleans it up but then it miselses the minus sign in front

sometimes neither works (spaces or tabs)

 

[daveyc18]

Loading Google Sheets

 

[Sufiyan97]

Have you tried formula in post #3?

 

[daveyc18]

Have you tried formula in post #3?

yes unfortunately didn't work

 

[Sufiyan97]

Try

SAMPLE (2).xlsx
	C	D	E	F	G	H	I	J
1
2		- 0.01	-2195242500	-2195242500	-0.01	-2195242500	-2195242500
3		-3854864560	-1630265459	2,224,599,100.49	-3854864560	-1630265459	2224599100
4		-7300774936	-5800826166	1,499,948,769.25	-7300774936	-5800826166	1499948769
5		- 119,448,676.97	- 69,226,324.31	50,222,352.66	-119448677	-69226324.31	50222352.66
6		-2384140065	-2507212272	- 123,072,206.40	-2384140065	-2507212272	-123072206.4
7		- 51,045,800.03	- 762,081,760.03	- 711,035,960.00	-51045800.03	-762081760	-711035960
8		- 135,400,000.01	- 0.01	135,400,000.00	-135400000	-0.01	135400000
9
10
Sheet2

Cell Formulas
Range	Formula
G2:I8	G2	=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2," ","")," ","")," ","")+0

 

[JvdV]

Maybe:

=--SUBSTITUTE(D1:F7,CHAR(160),)

 

[Fluff]

How about

SAMPLE (2).xlsx
	D	E	F	G	H	I
1	- 0.01	-2,195,242,500.01	-2,195,242,500.00	-0.01	-2195242500	-2195242500
2	-3,854,864,559.50	-1,630,265,459.01	2,224,599,100.49	-3854864560	-1630265459	2224599100
3	-7,300,774,935.53	-5,800,826,166.27	1,499,948,769.25	-7300774936	-5800826166	1499948769
4	- 119,448,676.97	- 69,226,324.31	50,222,352.66	-119448677	-69226324.31	50222352.66
5	-2,384,140,065.40	-2,507,212,271.80	- 123,072,206.40	-2384140065	-2507212272	-123072206.4
6	- 51,045,800.03	- 762,081,760.03	- 711,035,960.00	-51045800.03	-762081760	-711035960
7	- 135,400,000.01	- 0.01	135,400,000.00	-135400000	-0.01	135400000
Sheet1

Cell Formulas
Range	Formula
G1:I7	G1	=SUBSTITUTE(D1,CHAR(160),"")+0