Text Separation

[dodokh]

Hello,

I have the following text in A1:

323 Corr, Mary George 1 Y 10000 - Ladie 10000.00 07/08/10

How can we get the following:

B1: 323
C1: Corr, Mary George
D1: 1 Y
E1: 10000

Thank in advance.

 

[Robert Mika]

Would this pattern always be the same?
Could you post few more example?

 

[asking]

Hi,
Excel has, in the data menu text to columns
Select A1, click text to columns, select fixed width, click next, drag the arrows to separate the data, click next, select destinination B1, click in the header in the box the data that you do not wish to import, tick the button do not import column, click ok.

Cheers

 

[dodokh]

Would this pattern always be the same?
Could you post few more example?

816 Zainelabdin, Ibrahim Sal 1 Y 8000- Men Annual 8000.00 02/08/10
817 Al-Issa, Faisal 1 Y 8000- Men Annual 8000.00 02/08/10
818 Droubi, Nawaf Tarek 1 M 1800 - Men Monthly 1800.00 08/08/10

 

[Haseeb Avarakkan]

Hello, Try this,

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">323 Corr, Mary George 1 Y 10000 - Ladie 10000.00 07/08/10</td><td style="text-align: right;;">323</td><td style=";">Corr, Mary George</td><td style=";">1 Y</td><td style="text-align: right;;">10000</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Zainelabdin, Ibrahim Sal 1 Y 8000- Men Annual 8000.00 02/08/10</td><td style=";"></td><td style=";">Zainelabdin, Ibrahim Sal</td><td style=";">1 Y</td><td style="text-align: right;;">8000</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Al-Issa, Faisal 1 Y 8000- Men Annual 8000.00 02/08/10</td><td style=";"></td><td style=";">Al-Issa, Faisal</td><td style=";">1 Y</td><td style="text-align: right;;">8000</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Droubi, Nawaf Tarek 1 M 1800 - Men Monthly 1800.00 08/08/10</td><td style=";"></td><td style=";">Droubi, Nawaf Tarek</td><td style=";">1 M</td><td style="text-align: right;;">1800</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B1</th><td style="text-align:left">=IF(<font color="Blue">ISNUMBER(<font color="Red">--LEFT(<font color="Green">A1,FIND(<font color="Purple">" ",A1</font>)-1</font>)</font>),--LEFT(<font color="Red">A1,FIND(<font color="Green">" ",A1</font>)-1</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C1</th><td style="text-align:left">=TRIM(<font color="Blue">REPLACE(<font color="Red">REPLACE(<font color="Green">A1,1,SEARCH(<font color="Purple">B1,A1</font>)+LEN(<font color="Purple">B1</font>)-1,""</font>),FIND(<font color="Green">D1,REPLACE(<font color="Purple">A1,1,SEARCH(<font color="Teal">B1,A1</font>)+LEN(<font color="Teal">B1</font>)-1,""</font>)</font>),250,""</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D1</th><td style="text-align:left">=TRIM(<font color="Blue">RIGHT(<font color="Red">SUBSTITUTE(<font color="Green">TRIM(<font color="Purple">REPLACE(<font color="Teal">A1,FIND(<font color="#FF00FF">E1,A1</font>),250,""</font>)</font>)," ",REPT(<font color="Purple">" ",100</font>)</font>),200</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E1</th><td style="text-align:left">=LOOKUP(<font color="Blue">1E+100,--RIGHT(<font color="Red">SUBSTITUTE(<font color="Green">REPLACE(<font color="Purple">A1,SEARCH(<font color="Teal">IF(<font color="#FF00FF">ISERROR(<font color="Navy">SEARCH(<font color="Blue">"Men",A1</font>)</font>),"Ladie","Men"</font>),A1</font>),250,""</font>),"-",""</font>),ROW(<font color="Green">$A$1:$A$20</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
Copy down.

 

[dodokh]

Hello, Try this,

Excel 2007<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px"><colgroup><col style="background-color: #E0E0F0" width="25px"><col><col><col><col><col></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th>
</th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style=";">323 Corr, Mary George 1 Y 10000 - Ladie 10000.00 07/08/10</td><td style="text-align: right;;">323</td><td style=";">Corr, Mary George</td><td style=";">1 Y</td><td style="text-align: right;;">10000</td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style=";">Zainelabdin, Ibrahim Sal 1 Y 8000- Men Annual 8000.00 02/08/10</td><td style=";">
</td><td style=";">Zainelabdin, Ibrahim Sal</td><td style=";">1 Y</td><td style="text-align: right;;">8000</td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style=";">Al-Issa, Faisal 1 Y 8000- Men Annual 8000.00 02/08/10</td><td style=";">
</td><td style=";">Al-Issa, Faisal</td><td style=";">1 Y</td><td style="text-align: right;;">8000</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style=";">Droubi, Nawaf Tarek 1 M 1800 - Men Monthly 1800.00 08/08/10</td><td style=";">
</td><td style=";">Droubi, Nawaf Tarek</td><td style=";">1 M</td><td style="text-align: right;;">1800</td></tr></tbody></table>

Sheet3​

<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" width="85%" cellpadding="2.5px"><tbody><tr><td style="padding:6px">Worksheet Formulas<table rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6" width="100%" cellpadding="2.5px"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">B1</th><td style="text-align:left">=IF(ISNUMBER(--LEFT(A1,FIND(" ",A1)-1)),--LEFT(A1,FIND(" ",A1)-1),"")</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">C1</th><td style="text-align:left">=TRIM(REPLACE(REPLACE(A1,1,SEARCH(B1,A1)+LEN(B1)-1,""),FIND(D1,REPLACE(A1,1,SEARCH(B1,A1)+LEN(B1)-1,"")),250,""))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">D1</th><td style="text-align:left">=TRIM(RIGHT(SUBSTITUTE(TRIM(REPLACE(A1,FIND(E1,A1),250,""))," ",REPT(" ",100)),200))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">E1</th><td style="text-align:left">=LOOKUP(1E+100,--RIGHT(SUBSTITUTE(REPLACE(A1,SEARCH(IF(ISERROR(SEARCH("Men",A1)),"Ladie","Men"),A1),250,""),"-",""),ROW($A$1:$A$20)))</td></tr></tbody></table></td></tr></tbody></table>

Copy down.

Thank you very much, it's working perfectly.

Can you help me to get the date in F1 ?

 

[Haseeb Avarakkan]

If it is always 8 character,

=RIGHT(A1,8)+0

If it is may have different format, like 08/08/2011, 08/08/11... and date is always after the last space, try his

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",50)),50))+0

Change the cell format to date.