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]

 

[decadence]

Hi, Are the Columns/Rows of data going to be static or variable? Also is there any other data within the sheet?

 

[jtakw]

Hi,

If your data is Always in the structure as your samples, you can use this formula:

Book1
	A	B
1	D DXH110817 DOE JOHN	DDXH110817DOE JOHN
2	D RXP011916 DOE JANE	DRXP011916DOE JANE
3	D 173616018 DOE JAKE	D173616018DOE JAKE
Sheet65

Cell Formulas
Range	Formula
B1	=SUBSTITUTE(SUBSTITUTE(A1," ","",1)," ","",1)

 

[Manny74]

I believe static.

So the data looks like this:

[TABLE="width: 711"]
<tbody>[TR]
[TD]D[/TD]
[TD]DXH110817[/TD]
[TD]DOE[/TD]
[TD]JOHN[/TD]
[TD]X[/TD]
[TD]20171108[/TD]
[TD]06[/TD]
[TD]Z20.6[/TD]
[TD="align: right"]20171213[/TD]
[TD]958577066[/TD]
[TD]XHV[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]RXP011916[/TD]
[TD]DOE[/TD]
[TD]JANE[/TD]
[TD]X[/TD]
[TD]20160119[/TD]
[TD]06[/TD]
[TD]Z20.6[/TD]
[TD="align: right"]20171017[/TD]
[TD]954772357[/TD]
[TD]XHV[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]173616018[/TD]
[TD]DOE[/TD]
[TD]JUDE[/TD]
[TD]L[/TD]
[TD]20150327[/TD]
[TD]06[/TD]
[TD]Z21[/TD]
[TD="align: right"]20170403[/TD]
[TD]953854210[/TD]
[TD]XHV[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]AEO110613[/TD]
[TD]DOE[/TD]
[TD]JILL[/TD]
[TD]E[/TD]
[TD]20131106[/TD]
[TD]06[/TD]
[TD]B20[/TD]
[TD]20150922[/TD]
[TD]952860686[/TD]
[TD]XHV[/TD]
[/TR]
</tbody><colgroup><col span="2"><col><col><col span="2"><col><col><col span="2"><col></colgroup>[/TABLE]

 

[Manny74]

And it should look like this:

[TABLE="width: 492"]
<tbody>[TR]
[TD]DDXH110817DOE[/TD]
[TD]JOHN[/TD]
[TD]X2017110806Z20.6[/TD]
[TD]20171213[/TD]
[TD]958577066XHV[/TD]
[/TR]
[TR]
[TD]DRXP011916DOE[/TD]
[TD]JANE[/TD]
[TD]X2016011906Z20.6[/TD]
[TD]20171017[/TD]
[TD]954772357XHV[/TD]
[/TR]
[TR]
[TD]D173616018DOE[/TD]
[TD]JUDE[/TD]
[TD]L2015032706Z21[/TD]
[TD]20170403[/TD]
[TD]953854210XHV[/TD]
[/TR]
[TR]
[TD]DAEO110613DOE[/TD]
[TD]JILL[/TD]
[TD]E2013110606B20[/TD]
[TD]20150922[/TD]
[TD]952860686XHV[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col></colgroup>[/TABLE]

 

[jtakw]

Is each line of data string in ONE cell?

 

[Manny74]

The data looks like this:

[TABLE="width: 912"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD] C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]DXH110817[/TD]
[TD] DOE[/TD]
[TD]JOHN[/TD]
[TD]X[/TD]
[TD]20171108[/TD]
[TD]06[/TD]
[TD]Z20.6[/TD]
[TD]20171213[/TD]
[TD]958577066[/TD]
[TD]XHV[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]RXP011916[/TD]
[TD] DOE[/TD]
[TD]JANE[/TD]
[TD]X[/TD]
[TD]20160119[/TD]
[TD]06[/TD]
[TD]Z20.6[/TD]
[TD]20171017[/TD]
[TD]954772357[/TD]
[TD]XHV[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]173616018[/TD]
[TD] DOE[/TD]
[TD]JUDE[/TD]
[TD]L[/TD]
[TD]20150327[/TD]
[TD]06[/TD]
[TD]Z21[/TD]
[TD]20170403[/TD]
[TD]953854210[/TD]
[TD]XHV[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]AEO110613[/TD]
[TD] DOE[/TD]
[TD]JILL[/TD]
[TD]E[/TD]
[TD]20131106[/TD]
[TD]06[/TD]
[TD]B20[/TD]
[TD]20150922[/TD]
[TD]952860686[/TD]
[TD]XHV[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col span="3"><col span="3"></colgroup>[/TABLE]

 

[jtakw]

Are you looking to REPLACE the original data as the way you show in Post #5 ?
Or, can we place the NEW modified string in other Columns/Cells?
Also, the way you want the results in Post #5 , is that each line in ONE cell, or are they in separate Columns?

Are you looking for Formula, or VBA?

 

[Manny74]

Are you looking to REPLACE the original data as the way you show in Post #5 ?
Or, can we place the NEW modified string in other Columns/Cells?
Also, the way you want the results in Post #5 , is that each line in ONE cell, or are they in separate Columns?

Are you looking for Formula, or VBA?

I'm looking to remove spaces in between columns (cells).....
I'm going from 11 columns to about 5 columns....
I'm looking for a formula...

 

[jtakw]

Then it's pretty straight forward:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
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	=CONCATENATE(A1,B1,C1," ",D1)
N1	=E1&F1&G1&H1
O1	=I1
P1	=J1&K1

Formulas copied down.