Seperate text from values?

[Bilingual]

Hi everybody, i have following problem, i have a lot of text strings mixed with numeric values, an example:

DK-NOTA 8771 ISTAPPERIET 06.10 46,00- 9.987,12

In this string, i want to separate values in a separate column, but there has to be some conditions.

The ideal would be that the separation firstly occurs after the date format, here 06.10 and then in two separate columns like this:

DK-NOTA 8771 ISTAPPERIET 06.10 46,00 - 9.987,12

 

[Sandeep Warrier]

Hi

Do you mean a split like

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="10px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">DK-NOTA 8771 ISTAPPERIET 06.10</td><td style=";">46,00- 9.987,12</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 #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />


Questions:
1. Is the date format always dd.mm (or mm.dd)?
2. Can a "." appear before the date in the text?

 

[Bilingual]

Hi Sandeep

1. Yes
2. No

 

[Sandeep Warrier]

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="10px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Data</td><td style="font-weight: bold;;">Split 1</td><td style="font-weight: bold;;">Split 2</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">DK-NOTA 8771 ISTAPPERIET 06.10 46,00- 9.987,12</td><td style=";">DK-NOTA 8771 ISTAPPERIET 06.10</td><td style=";">46,00- 9.987,12</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 #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</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: #BBB"><thead><tr style=" background-color: #DAE7F5;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: #DAE7F5;color: #161120">B2</th><td style="text-align:left">=LEFT(<font color="Blue">A2,FIND(<font color="Red">" ",A2,FIND(<font color="Green">".",A2</font>)</font>)-1</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C2</th><td style="text-align:left">=TRIM(<font color="Blue">SUBSTITUTE(<font color="Red">A2,B2,""</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[Bilingual]

I am afraid it does not work, it separates at the wrong place:


DK-NOTA 8771 ISTAPPERIET 06

.10 46,00- 9.987,12

 

[Sandeep Warrier]

What is the exact formula you used?

Do you have any extra spaces around the "."?

 

[bolo]

FOrget i said anything. Must learn to read properly!!!!

 

[Bilingual]

Sorry my mistake, it works fine, thanks a lot