Remove extra space in a cell with formula

[RAJESH1960]

Hello, I have too many rows in a sheet where some cells have an extra space. I want to remove it with the help of a formula in the same column or an adjacent column by which I can remove the extra space. I have already tried trim and substitute function but it doesn't help.

Query to remove space.xlsm
	A
1	X4361
2	X5281
3	X6805
4	X9574
5	X9596
6	X11134
7	X1634
8	X2682
9	X9127
10	X9233
11	X9338
12	X9441
13	X9514
14	X9365
15	X9387
16	X9602
17	X9671
18	X9708
19	X9115
20	X9169
21	X9564
22	X9926
23	X10488
24	X8975
25	X9237
26	X9577
27	X9609
Query

 

[Rick Rothstein]

More than likely you have something called "non-breaking spaces" (ASCII 160). You cannot put a formula in the same column because a cell can only contain one thing... a formula or a constant (ignoring hyperlinks) but not both at the same time. So, you will need to put this formula in a different column (you can then copy paste its values over the constants in what I am assuming is Column )...

=SUBSTITUTE(A4,CHAR(160),"")

 

[RAJESH1960]

More than likely you have something called "non-breaking spaces" (ASCII 160). You cannot put a formula in the same column because a cell can only contain one thing... a formula or a constant (ignoring hyperlinks) but not both at the same time. So, you will need to put this formula in a different column (you can then copy paste its values over the constants in what I am assuming is Column )...

=SUBSTITUTE(A4,CHAR(160),"")

Not working Rick. Still after applying the formula in the adjacent blank column & copy paste special, the space is still not removed.

 

[alansidman]

Using your sample, I brought into Power Query and ran a Trim against the range. It removed all extra spaces.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"Column1", Text.Trim, type text}})
in
    #"Trimmed Text"

See below results

Book9
	A	B	C	D	E
1	Column1			Column1	Column2
2	X4361	5		X4361	5
3	X5281	5		X5281	5
4	X6805	5		X6805	5
5	X9574	6		X9574	5
6	X9596	6		X9596	5
7	X11134	6		X11134	6
8	X1634	5		X1634	5
9	X2682	5		X2682	5
10	X9127	5		X9127	5
11	X9233	5		X9233	5
12	X9338	6		X9338	5
13	X9441	6		X9441	5
14	X9514	5		X9514	5
15	X9365	5		X9365	5
16	X9387	5		X9387	5
17	X9602	6		X9602	5
18	X9671	5		X9671	5
19	X9708	5		X9708	5
20	X9115	5		X9115	5
21	X9169	5		X9169	5
22	X9564	6		X9564	5
23	X9926	5		X9926	5
24	X10488	6		X10488	6
25	X8975	5		X8975	5
26	X9237	5		X9237	5
27	X9577	6		X9577	5
28	X9609	5		X9609	5
Sheet1

Cell Formulas
Range	Formula
B2:B28	B2	=LEN(A2)
E2:E28	E2	=LEN([@Column1])

 

[RAJESH1960]

Using your sample, I brought into Power Query and ran a Trim against the range. It removed all extra spaces.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"Column1", Text.Trim, type text}})
in
    #"Trimmed Text"

See below results

Book9
	A	B	C	D	E
1	Column1			Column1	Column2
2	X4361	5		X4361	5
3	X5281	5		X5281	5
4	X6805	5		X6805	5
5	X9574	6		X9574	5
6	X9596	6		X9596	5
7	X11134	6		X11134	6
8	X1634	5		X1634	5
9	X2682	5		X2682	5
10	X9127	5		X9127	5
11	X9233	5		X9233	5
12	X9338	6		X9338	5
13	X9441	6		X9441	5
14	X9514	5		X9514	5
15	X9365	5		X9365	5
16	X9387	5		X9387	5
17	X9602	6		X9602	5
18	X9671	5		X9671	5
19	X9708	5		X9708	5
20	X9115	5		X9115	5
21	X9169	5		X9169	5
22	X9564	6		X9564	5
23	X9926	5		X9926	5
24	X10488	6		X10488	6
25	X8975	5		X8975	5
26	X9237	5		X9237	5
27	X9577	6		X9577	5
28	X9609	5		X9609	5
Sheet1

Cell Formulas
Range	Formula
B2:B28	B2	=LEN(A2)
E2:E28	E2	=LEN([@Column1])

Alansidman, I am still learning excel. I have no idea of power query. Can I apply a formula to do the above.?

 

[Michael M]

If it is actually a space try using

=TRIM(A2)

drag down as required

 

[RAJESH1960]

If it is actually a space try using

=TRIM(A2)

drag down as required

I already tried that and also substitute function, but it ain't working

 

[jtakw]

Hi,

Seems you have Nonprintable (invisible) characters, try:

Book3.xlsx
	A	B	C	D
1	X4361	X4361	5	5
2	X5281	X5281	5	5
3	X6805	X6805	5	5
4	X9574	X9574	6	5
5	X9596	X9596	6	5
6	X11134	X11134	6	6
7	X1634	X1634	5	5
8	X2682	X2682	5	5
9	X9127	X9127	5	5
10	X9233	X9233	5	5
11	X9338	X9338	6	5
12	X9441	X9441	6	5
13	X9514	X9514	5	5
14	X9365	X9365	5	5
15	X9387	X9387	5	5
16	X9602	X9602	6	5
17	X9671	X9671	5	5
18	X9708	X9708	5	5
19	X9115	X9115	5	5
20	X9169	X9169	5	5
21	X9564	X9564	6	5
22	X9926	X9926	5	5
23	X10488	X10488	6	6
24	X8975	X8975	5	5
25	X9237	X9237	5	5
26	X9577	X9577	6	5
27	X9609	X9609	5	5
Sheet714

Cell Formulas
Range	Formula
B1:B27	B1	=CLEAN(A1)
C1:D27	C1	=LEN(A1)

 

[RAJESH1960]

Hi,

Seems you have Nonprintable (invisible) characters, try:

Book3.xlsx
	A	B	C	D
1	X4361	X4361	5	5
2	X5281	X5281	5	5
3	X6805	X6805	5	5
4	X9574	X9574	6	5
5	X9596	X9596	6	5
6	X11134	X11134	6	6
7	X1634	X1634	5	5
8	X2682	X2682	5	5
9	X9127	X9127	5	5
10	X9233	X9233	5	5
11	X9338	X9338	6	5
12	X9441	X9441	6	5
13	X9514	X9514	5	5
14	X9365	X9365	5	5
15	X9387	X9387	5	5
16	X9602	X9602	6	5
17	X9671	X9671	5	5
18	X9708	X9708	5	5
19	X9115	X9115	5	5
20	X9169	X9169	5	5
21	X9564	X9564	6	5
22	X9926	X9926	5	5
23	X10488	X10488	6	6
24	X8975	X8975	5	5
25	X9237	X9237	5	5
26	X9577	X9577	6	5
27	X9609	X9609	5	5
Sheet714

Cell Formulas
Range	Formula
B1:B27	B1	=CLEAN(A1)
C1:D27	C1	=LEN(A1)

Thanks jtakw, Clean option worked. And I also found another way without using a formula. Select the data, select text to columns, delimited, space, finish. Your formula is better, My formula will work only with space in the end of a cell with no other spaces in the middle. Thanks once again

 

[jtakw]

You're welcome, glad to help.