Prev: GETPIVOTDATA and (blank)
Next: how can i transfer data from one sheet to another without blank li
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" <Manos(a)discussions.microsoft.com> 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
 | 
Pages: 1
Prev: GETPIVOTDATA and (blank)
Next: how can i transfer data from one sheet to another without blank li