Delimite the text in cell

[G2K]

Hi,

i have to delimite the following data, written in a single cell.
Barclays PLC
Citi
Deutsche Bank AG
Goldman Sachs & Co
HSBC Holdings PLC
Merrill Lynch
Morgan Stanley & Co
RBS
UBS

excel showing small rectangle between two lines. how to delimite such text....

 

[vinod9111]

try this in b1 if your data is in a1

=SUBSTITUTE(a1,CHAR(10),"#")

then you could delimit the data with #

 

[Peter_SSs]

See if this helps:
http://www.mrexcel.com/forum/showpost.php?p=1690843&postcount=10

 

[Yard]

Maybe try

=CLEAN(A1)

or you could find out the character code for the "small rectangle" by copying one into another cell and using

=CODE(B1)

to get the character number. When you have that, you can use

=SUBSTITUTE(A1,CHAR(10)," ")

where A1 contains your original string and 10 is the character number of the small rectangle.

 

[G2K]

thanks to all of you,

it's working fine, but it has created a new problem for me as it is pasted in different columns. however, i need all this information in rows.

i don't know whether it is possible or not

if not, then i need a macro to insert new rows and paste this string in inserted rows.

thanks

 

[Peter_SSs]

thanks to all of you,

it's working fine, but it has created a new problem for me as it is pasted in different columns. however, i need all this information in rows.

i don't know whether it is possible or not

if not, then i need a macro to insert new rows and paste this string in inserted rows.

thanks

Can you just select the newly delimited data, Copy, select a new location then Edit|Paste Special...|Transpose|OK

 

[G2K]

thanks for looking in to this problem

but i have more than 2500 rows. and no one can copy the delimited data and paste it thausand times

may be i am not getting you properly. below is the sample data please check it and suggest.....

current format
<TABLE dir=ltr borderColor=#01ad00 cellSpacing=1 cellPadding=2 width=890 border=1><TBODY><TR><TD width="8%" bgColor=#99ccff height=16>

ASD​

​

​

​

​

</TD><TD width="8%" bgColor=#99ccff height=16>

QWE​

​

​

​

​

</TD><TD width="9%" bgColor=#99ccff height=16>

RTY​

​

​

​

​

</TD><TD width="8%" bgColor=#99ccff height=16>

UIO​

​

​

​

​

</TD><TD width="8%" bgColor=#99ccff height=16>

ASD​

​

​

​

​

</TD><TD width="8%" bgColor=#99ccff height=16>

FGH​

​

​

​

​

</TD><TD width="49%" bgColor=#99ccff height=16>

RBS
Barclays PLC
Royal Bank of Canada
Lloyds TSB Bank PLC​

​

​

​

​

</TD></TR></TBODY></TABLE>

Desired format

<TABLE dir=ltr borderColor=#04ad00 cellSpacing=1 cellPadding=2 width=595 border=1><TBODY><TR><TD width="13%" bgColor=#99ccff height=16>

ASD​

​

​

​

​

</TD><TD width="13%" bgColor=#99ccff height=16>

QWE​

​

​

​

​

</TD><TD width="13%" bgColor=#99ccff height=16>

RTY​

​

​

​

​

</TD><TD width="13%" bgColor=#99ccff height=16>

UIO​

​

​

​

​

</TD><TD width="13%" bgColor=#99ccff height=16>

ASD​

​

​

​

​

</TD><TD width="13%" bgColor=#99ccff height=16>

FGH​

​

​

​

​

</TD><TD width="24%" bgColor=#99ccff height=16>

RBS​

​

​

​

​

</TD></TR><TR><TD width="13%" bgColor=#99ccff height=16>

ASD​

​

​

​

​

</TD><TD width="13%" bgColor=#99ccff height=16>

QWE​

​

​

​

​

</TD><TD width="13%" bgColor=#99ccff height=16>

RTY​

​

​

​

​

</TD><TD width="13%" bgColor=#99ccff height=16>

UIO​

​

​

​

​

</TD><TD width="13%" bgColor=#99ccff height=16>

ASD​

​

​

​

​

</TD><TD width="13%" bgColor=#99ccff height=16>

FGH​

​

​

​

​

</TD><TD width="24%" bgColor=#99ccff height=16>

Barclays PLC​

​

​

​

​

</TD></TR><TR><TD width="13%" bgColor=#99ccff height=16>

ASD​

​

​

​

​

</TD><TD width="13%" bgColor=#99ccff height=16>

QWE​

​

​

​

​

</TD><TD width="13%" bgColor=#99ccff height=16>

RTY​

​

​

​

​

</TD><TD width="13%" bgColor=#99ccff height=16>

UIO​

​

​

​

​

</TD><TD width="13%" bgColor=#99ccff height=16>

ASD​

​

​

​

​

</TD><TD width="13%" bgColor=#99ccff height=16>

FGH​

​

​

​

​

</TD><TD width="24%" bgColor=#99ccff height=16>

Royal Bank of Canada​

​

​

​

​

</TD></TR><TR><TD width="13%" bgColor=#99ccff height=16>

ASD​

​

​

​

​

</TD><TD width="13%" bgColor=#99ccff height=16>

QWE​

​

​

​

​

</TD><TD width="13%" bgColor=#99ccff height=16>

RTY​

​

​

​

​

</TD><TD width="13%" bgColor=#99ccff height=16>

UIO​

​

​

​

​

</TD><TD width="13%" bgColor=#99ccff height=16>

ASD​

​

​

​

​

</TD><TD width="13%" bgColor=#99ccff height=16>

FGH​

​

​

​

​

</TD><TD width="24%" bgColor=#99ccff height=16>

Lloyds TSB Bank PLC​

​

​

​

​

</TD></TR></TBODY></TABLE>


Thanks

 

[Peter_SSs]

I have assumed these are columns A:G and that there is a heading row in row 1.

Test on a copy of your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Rearrange()<br>    <SPAN style="color:#00007F">Dim</SPAN> aSplit<br>    <SPAN style="color:#00007F">Dim</SPAN> lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, newrows <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    lr = Range("A" & Rows.Count).End(xlUp).Row<br>    <SPAN style="color:#00007F">For</SPAN> r = lr <SPAN style="color:#00007F">To</SPAN> 2 <SPAN style="color:#00007F">Step</SPAN> -1<br>        aSplit = Split(Cells(r, "G").Value, Chr(10))<br>        newrows = <SPAN style="color:#00007F">UBound</SPAN>(aSplit)<br>        Rows(r + 1).Resize(newrows).Insert<br>        Cells(r, "G").Resize(newrows + 1) = Application.Transpose(aSplit)<br>        Cells(r + 1, 1).Resize(newrows, 6).Value = Cells(r, 1).Resize(, 6).Value<br>    <SPAN style="color:#00007F">Next</SPAN> r<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>


Sheet before code was run:

Excel Workbook
	A	B	C	D	E	F	G
1	Header 1	Header 2	Header 3	Header 4	Header 5	Header 6	Header 7
2	ASD	QWE	RTY	UIO	ASD	FGH	RBSBarclays PLCRoyal Bank of CanadaLloyds TSB Bank PLC
3	x	x	x	x	x	x	RBSBarclays PLCRoyal Bank of CanadaLloyds TSB Bank PLCExtra one
4	y	y	y	y	y	y	RBSBarclays PLCRoyal Bank of CanadaLloyds TSB Bank PLC
Transpose (Before)

Sheet after code was run:

Excel Workbook
	A	B	C	D	E	F	G
1	Header 1	Header 2	Header 3	Header 4	Header 5	Header 6	Header 7
2	ASD	QWE	RTY	UIO	ASD	FGH	RBS
3	ASD	QWE	RTY	UIO	ASD	FGH	Barclays PLC
4	ASD	QWE	RTY	UIO	ASD	FGH	Royal Bank of Canada
5	ASD	QWE	RTY	UIO	ASD	FGH	Lloyds TSB Bank PLC
6	x	x	x	x	x	x	RBS
7	x	x	x	x	x	x	Barclays PLC
8	x	x	x	x	x	x	Royal Bank of Canada
9	x	x	x	x	x	x	Lloyds TSB Bank PLC
10	x	x	x	x	x	x	Extra one
11	y	y	y	y	y	y	RBS
12	y	y	y	y	y	y	Barclays PLC
13	y	y	y	y	y	y	Royal Bank of Canada
14	y	y	y	y	y	y	Lloyds TSB Bank PLC
Transpose (After)

 

[G2K]

thanks Peter,

it's working like a magic. you have changed my imagination to a small function. great........

however, there is one small problem.....date format are being changed and i am unable to format it. upper cell is in general format( whereas lower cell is in date format.

<TABLE dir=ltr borderColor=#05ad00 cellSpacing=1 cellPadding=2 width=99 border=1><TBODY><TR><TD bgColor=#99ccff height=16>06/24/09

​

</TD></TR><TR><TD bgColor=#99ccff height=16>24/06/2009

</TD></TR></TBODY></TABLE>​

 

[Peter_SSs]

thanks Peter,

it's working like a magic. you have changed my imagination to a small function. great........

however, there is one small problem.....date format are being changed and i am unable to format it. upper cell is in general format( whereas lower cell is in date format.

<TABLE dir=ltr borderColor=#05ad00 cellSpacing=1 cellPadding=2 width=99 border=1><TBODY><TR><TD bgColor=#99ccff height=16>06/24/09

​

</TD></TR><TR><TD bgColor=#99ccff height=16>24/06/2009

</TD></TR></TBODY></TABLE>​

I don't understand where you are talking about (a particular column?). This looks nothing like your original sample. Which is the correct format?