Import underscore in coding every after 2 digits

M

Manos

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.... :)
 
J

Jacob Skaria

--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")
 
D

Dave Peterson

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Inserting comma(,) after every two digits of a number from right t 2
Splitting 1 cell into 2 based on underscore in text 5
Replacing spaces with underscore for specific expressions 8
Please assist... 3
Digits after decimal 2
Date format problems after csv import 2
Autofilter Wildcards Not Working on Number Column 2
How to get thousand seperator after every two digits 2

Top