From: Manos on 23 Apr 2010 04:41 Dear All I have a fixed coding with 8 digits. (ie: 40000000). I need to change this and split it importing underscore every two digits in order to look like (ie.: 40_00_00_00) Any clever way to do it for 25000 rows? Thanks in advance for your time.... :) From: Jacob Skaria on 23 Apr 2010 04:53 --To retain these as numbers; select the range and from menu Format>Cells>Custom and type as below 00"_"00"_"00"_"00 --OR as text; you can use a formula in ColB to convert =LEFT(A1,2)&"_" & MID(A1,3,2)&"_"&MID(A1,5,2)&"_"&RIGHT(A1,2) If you are trying to import this to elsewhere using code you can try the format function as below Format(range("A1"),"00_00_00_00") -- Jacob (MVP - Excel) "Manos" wrote: > Dear All > > I have a fixed coding with 8 digits. (ie: 40000000). > I need to change this and split it importing underscore every two digits in > order to look like (ie.: 40_00_00_00) > Any clever way to do it for 25000 rows? > > Thanks in advance for your time.... :) From: ozgrid.com on 23 Apr 2010 04:57 =LEFT(A1,2) & "_" & MID(A1,2,2) & "_" & MID(A1,6,2) & "_" & RIGHT(A1,2) -- Regards Dave Hawley www.ozgrid.com "Manos" wrote in message news:1D04D522-E6C5-4EF0-8B13-4F6C30DC72B2(a)microsoft.com...> Dear All > > I have a fixed coding with 8 digits. (ie: 40000000). > I need to change this and split it importing underscore every two digits > in > order to look like (ie.: 40_00_00_00) > Any clever way to do it for 25000 rows? > > Thanks in advance for your time.... :) From: Teethless mama on 23 Apr 2010 10:17 =SUBSTITUTE(TEXT(A1,"00-00-00-00"),"-","_") "Manos" wrote: > Dear All > > I have a fixed coding with 8 digits. (ie: 40000000). > I need to change this and split it importing underscore every two digits in > order to look like (ie.: 40_00_00_00) > Any clever way to do it for 25000 rows? > > Thanks in advance for your time.... :) From: Dave Peterson on 23 Apr 2010 11:08 Or just: =TEXT(A1,"00\_00\_00\_00") The backslash is the escape character to indicate that the next character should be treated as text--not a formatting character. Teethless mama wrote:> > =SUBSTITUTE(TEXT(A1,"00-00-00-00"),"-","_") > > "Manos" wrote: > > > Dear All > > > > I have a fixed coding with 8 digits. (ie: 40000000). > > I need to change this and split it importing underscore every two digits in > > order to look like (ie.: 40_00_00_00) > > Any clever way to do it for 25000 rows? > > > > Thanks in advance for your time.... :) -- Dave Peterson  |