Remove Spaces

[Manny74]

Hi All,

My data is supposed to look a certain way ( see below)

I need to remove spaces between D, the ID # (for example DXH110817) and the last name Doe.

I'm trying to find an efficient fast way of doing this since I have about 50 records in my data.

Any suggestions?

So right now, it looks like this:

[TABLE="width: 243"]
<tbody>[TR]
[TD]D[/TD]
[TD] DXH110817 DOE JOHN[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD] RXP011916 DOE JANE[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD] 173616018 DOE JAKE[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD]
But it should look like this: [TABLE="width: 178"]
<tbody>[TR]
[TD]DDXH110817DOE[/TD]
[TD]JOHN[/TD]
[/TR]
[TR]
[TD]DRXP011916DOE[/TD]
[TD]JANE[/TD]
[/TR]
[TR]
[TD]D173616018DOE[/TD]
[TD]JAKE[/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col><col><col></colgroup>[/TABLE]

 

[jtakw]

Oops, I missed the part where you said 5 columns (not 4):

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	D	DXH110817	DOE	JOHN	X	20171108	6	Z20.6	20171213	958577066	XHV		DDXH110817DOE	JOHN	X201711086Z20.6	20171213	958577066XHV
2	D	RXP011916	DOE	JANE	X	20160119	6	Z20.6	20171017	954772357	XHV		DRXP011916DOE	JANE	X201601196Z20.6	20171017	954772357XHV
3	D	173616018	DOE	JUDE	L	20150327	6	Z21	20170403	953854210	XHV		D173616018DOE	JUDE	L201503276Z21	20170403	953854210XHV
4	D	AEO110613	DOE	JILL	E	20131106	6	B20	20150922	952860686	XHV		DAEO110613DOE	JILL	E201311066B20	20150922	952860686XHV
Sheet65

Cell Formulas
Range	Formula
M1	=A1&B1&C1
N1	=D1
O1	=E1&F1&G1&H1
P1	=I1
Q1	=J1&K1

 

[Manny74]

Concatenate works! Thanks!

But in doing so, column G (06) should have 2 digits (zero and six), and when I concatenate, it removes the zero (0) and leaves it as 6....And I need the 06 as part of the data....

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
D	DXH110817	DOE	JOHN	X	Z20.6	XHV	DDXH110817DOE JOHN	X201711086Z20.6	958577066XHV
D	RXP011916	DOE	JANE	X	Z20.6	XHV	DRXP011916DOE JANE	X201601196Z20.6	954772357XHV
D	DOE	JUDE	L	Z21	XHV	D173616018DOE JUDE	L201503276Z21	953854210XHV
D	AEO110613	DOE	JILL	E	B20	XHV	DAEO110613DOE JILL	E201311066B20	952860686XHV

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]20171108[/TD]
[TD="align: right"]06[/TD]

[TD="align: right"]20171213[/TD]
[TD="align: right"]958577066[/TD]

[TD="align: right"][/TD]

[TD="align: right"]20171213[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]20160119[/TD]
[TD="align: right"]06[/TD]

[TD="align: right"]20171017[/TD]
[TD="align: right"]954772357[/TD]

[TD="align: right"][/TD]

[TD="align: right"]20171017[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]173616018[/TD]

[TD="align: right"]20150327[/TD]
[TD="align: right"]06[/TD]

[TD="align: right"]20170403[/TD]
[TD="align: right"]953854210[/TD]

[TD="align: right"][/TD]

[TD="align: right"]20170403[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]20131106[/TD]
[TD="align: right"]06[/TD]

[TD="align: right"]20150922[/TD]
[TD="align: right"]952860686[/TD]

[TD="align: right"][/TD]

[TD="align: right"]20150922[/TD]

</tbody>

Sheet65​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]M1[/TH]
[TD="align: left"]=CONCATENATE(A1,B1,C1," ",D1)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]N1[/TH]
[TD="align: left"]=E1&F1&G1&H1[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]O1[/TH]
[TD="align: left"]=I1[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]P1[/TH]
[TD="align: left"]=J1&K1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Formulas copied down.[/QUOTE]

 

[jtakw]

Is your G Column formatted as Text? Is it already showing the value as 06 ?

Seems to be working for me...

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	D	DXH110817	DOE	JOHN	X	20171108	06	Z20.6	20171213	958577066	XHV		DDXH110817DOE	JOHN	X2017110806Z20.6	20171213	958577066XHV
2	D	RXP011916	DOE	JANE	X	20160119	06	Z20.6	20171017	954772357	XHV		DRXP011916DOE	JANE	X2016011906Z20.6	20171017	954772357XHV
3	D	173616018	DOE	JUDE	L	20150327	06	Z21	20170403	953854210	XHV		D173616018DOE	JUDE	L2015032706Z21	20170403	953854210XHV
4	D	AEO110613	DOE	JILL	E	20131106	06	B20	20150922	952860686	XHV		DAEO110613DOE	JILL	E2013110606B20	20150922	952860686XHV
Sheet65

Cell Formulas
Range	Formula
M1	=A1&B1&C1
N1	=D1
O1	=E1&F1&G1&H1
P1	=I1
Q1	=J1&K1

 

[jtakw]

What do you see in the formula bar when you select G1, do you see 6 or 06 ?
If the Actual value in G is only 6, formatted to show as 06, then do this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
6	D	DXH110817	DOE	JOHN	X	20171108	6	Z20.6	20171213	958577066	XHV		DDXH110817DOE	JOHN	X2017110806Z20.6	20171213	958577066XHV
7	D	RXP011916	DOE	JANE	X	20160119	6	Z20.6	20171017	954772357	XHV		DRXP011916DOE	JANE	X2016011906Z20.6	20171017	954772357XHV
8	D	173616018	DOE	JUDE	L	20150327	6	Z21	20170403	953854210	XHV		D173616018DOE	JUDE	L2015032706Z21	20170403	953854210XHV
9	D	AEO110613	DOE	JILL	E	20131106	6	B20	20150922	952860686	XHV		DAEO110613DOE	JILL	E2013110606B20	20150922	952860686XHV
Sheet65

Cell Formulas
Range	Formula
M6	=A6&B6&C6
N6	=D6
O6	=E6&F6&TEXT(G6,"00")&H6
P6	=I6
Q6	=J6&K6

 

[Manny74]

When I select format cell and select Text, it changes 06, to 6....

 

[jtakw]

When I select format cell and select Text, it changes 06, to 6....

Sounds like the actual value is just 6, formatted to show as 06.

Did you try the formula in Post #14 ?