|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
April 23rd, 2010, 09:41 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Import underscore in coding every after 2 digits
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....  
|
|
#2
April 23rd, 2010, 09:53 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Import underscore in coding every after 2 digits
--To retain these as numbers; select the range and from menu
FormatCellsCustom 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....
|
|
#3
April 23rd, 2010, 09:57 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Import underscore in coding every after 2 digits
=LEFT(A1,2) & "_" & MID(A1,2,2) & "_" & MID(A1,6,2) & "_" & RIGHT(A1,2)
-- Regards Dave Hawley www.ozgrid.com "Manos" wrote in message ... 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....
|
|
#4
April 23rd, 2010, 03:17 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Import underscore in coding every after 2 digits
=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....
|
|
#5
April 23rd, 2010, 04:08 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Import underscore in coding every after 2 digits
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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
