break the number in the brackets

[kin]

Hello I have a column where there is data like this - (5-40-528) and would like to make the numbers in it 5-40-528 to appear in a different column

 

[kin]

this is where the file is http://www.box.net/shared/4jr96bzg7uek0v0trg28

 

[markmzz]

Hello I have a column where there is data like this - (5-40-528) and would like to make the numbers in it 5-40-528 to appear in a different column

Use the feature Text to columns of the Excel.

1-Select the range with numbers (A2:A31-in my example)

2-(for Excel 2007 +)-In Data tab/Group Tools of data, choose the Text to columns command.

2-(for Excel 2003)-Open the Data menu and choose the Text to columns command.

3-In step 1 of 3 dialog box of Text to columns, chose Delimited option and click Next.

4-In step 2 of 3 dialog box of Text to columns. Chose Other option, type - in the text box and click on Next.

5-In step 3 of 3 dialog box of Text to columns, type C2 in the Target text box, and then click Finish.


<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; 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="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>Numbers</TD><TD style="TEXT-ALIGN: right"></TD><TD>Number01</TD><TD>Number02</TD><TD>Number03</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>5-40-528</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">40</TD><TD style="TEXT-ALIGN: right">528</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>8-29-709</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right">29</TD><TD style="TEXT-ALIGN: right">709</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>1-30-634</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">30</TD><TD style="TEXT-ALIGN: right">634</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD>8-95-676</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right">95</TD><TD style="TEXT-ALIGN: right">676</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD>9-63-574</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">9</TD><TD style="TEXT-ALIGN: right">63</TD><TD style="TEXT-ALIGN: right">574</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD>8-62-684</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right">62</TD><TD style="TEXT-ALIGN: right">684</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD>6-99-414</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">99</TD><TD style="TEXT-ALIGN: right">414</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD>1-98-926</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">98</TD><TD style="TEXT-ALIGN: right">926</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD>1-85-892</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">85</TD><TD style="TEXT-ALIGN: right">892</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD>7-88-816</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">88</TD><TD style="TEXT-ALIGN: right">816</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD>4-74-193</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">74</TD><TD style="TEXT-ALIGN: right">193</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD>4-68-185</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">68</TD><TD style="TEXT-ALIGN: right">185</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD>6-80-253</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">80</TD><TD style="TEXT-ALIGN: right">253</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD>3-74-836</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">74</TD><TD style="TEXT-ALIGN: right">836</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">16</TD><TD>1-98-870</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">98</TD><TD style="TEXT-ALIGN: right">870</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">17</TD><TD>3-94-919</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">94</TD><TD style="TEXT-ALIGN: right">919</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">18</TD><TD>8-82-222</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right">82</TD><TD style="TEXT-ALIGN: right">222</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">19</TD><TD>8-58-925</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right">58</TD><TD style="TEXT-ALIGN: right">925</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">20</TD><TD>0-10-551</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">551</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">21</TD><TD>8-10-509</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">509</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">22</TD><TD>3-94-621</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">94</TD><TD style="TEXT-ALIGN: right">621</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">23</TD><TD>3-82-157</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">82</TD><TD style="TEXT-ALIGN: right">157</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">24</TD><TD>8-93-223</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right">93</TD><TD style="TEXT-ALIGN: right">223</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">25</TD><TD>3-30-344</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">30</TD><TD style="TEXT-ALIGN: right">344</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">26</TD><TD>9-62-169</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">9</TD><TD style="TEXT-ALIGN: right">62</TD><TD style="TEXT-ALIGN: right">169</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">27</TD><TD>8-52-501</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right">52</TD><TD style="TEXT-ALIGN: right">501</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">28</TD><TD>4-78-670</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">78</TD><TD style="TEXT-ALIGN: right">670</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">29</TD><TD>6-42-792</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">42</TD><TD style="TEXT-ALIGN: right">792</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">30</TD><TD>4-95-902</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">95</TD><TD style="TEXT-ALIGN: right">902</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">31</TD><TD>7-49-949</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">49</TD><TD style="TEXT-ALIGN: right">949</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">32</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">33</TD><TD>********</TD><TD>********</TD><TD>********</TD><TD>********</TD><TD>********</TD></TR></TBODY></TABLE>



Markmzz

 

[Marcelo Branco]

Hi,

If the string is always at the right-end of your text and as (x-xx-xxx), maybe this formula

=SUBSTITUTE(RIGHT(A2,9),")","")

copy down

HTH

M.

 

[kin]

I don t want 3 separate columns just one. So that the number will be shown 5-30-071

 

[Marcelo Branco]

I don t want 3 separate columns just one. So that the number will be shown 5-30-071

Hi kin,

Have you tried my formula?

M.

 

[Sektor]

May be this? UDF.

[COLOR="Blue"]Function[/COLOR] Separate(Str [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]) [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]
    [COLOR="Blue"]With[/COLOR] CreateObject("VBScript.RegExp")
        .Pattern = "(\d+)-(\d+)-(\d+)"
        Separate = .Execute(Str)(0)
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Function[/COLOR]

 

[Rick Rothstein]

I think this formula will do what you want...

=MID(A1,2,LEN(A1)-2)

 

[Rick Rothstein]

May be this? UDF.

[COLOR=blue]Function[/COLOR] Separate(Str [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR]) [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR]
    [COLOR=blue]With[/COLOR] CreateObject("VBScript.RegExp")
        .Pattern = "(\d+)-(\d+)-(\d+)"
        Separate = .Execute(Str)(0)
    [COLOR=blue]End[/COLOR] [COLOR=blue]With[/COLOR]
[COLOR=blue]End[/COLOR] [COLOR=blue]Function[/COLOR]

Assuming the OP wants VB code, why not something a little simpler...

Function Separate(Str As String) As String
    Separate = Mid(Str, 2, Len(Str) - 2)
End Function

 

[Sektor]

Or this way...

[COLOR="Blue"]Function[/COLOR] Separate(Str [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]) [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]
    Separate = Replace(Replace(Str, "(", ""), ")", "")
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Function[/COLOR]