Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question on Text to Column Function
Hi, I have some problem with the data after perform Text to Column Function. Eg: I have this no = 123456789 I want to add "-" in between become 1-234-567-89 So i Use Text To Column Function to separate 9 digit no to 4 column. Then use this formula to combine: [ =Column1&"-"&Column2&"-"&Column3&"-"&Column4 ] Problem: when the no contain "0" in some location, like 102500301, the no in column2 become 25, column3 become 3 and column4 become 1. after combine will become 1-25-3-1 (Actual needed is 1-025-003-01) Can some one help to solve this problem (what code can be aded in VBA to solve this)? Regards YL Chuah -- ylchuah ------------------------------------------------------------------------ ylchuah's Profile: http://www.excelforum.com/member.php...o&userid=25341 View this thread: http://www.excelforum.com/showthread...hreadid=396025 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question on Text to Column Function
Is there some specific reason why you are using Text To Columns to do this?
You could just use a formual like: =LEFT(A1)&"-"&MID(A1,2,3)&"-"&MID(A1,5,3)&"-"&RIGHT(A1,2) If there is some other reason why you are using text to columns then set the column data types to text. With VBA (assuming you are using fixed width) then the code would look something like: Range("A1").TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 2), Array(1, 2), Array(4, 2), Array(7, 2)), _ TrailingMinusNumbers:=True where the second argument in the array statement (i.e. Array(0,2) etc) sets the column property to xlTextFormat. Hope this helps Rowan "ylchuah" wrote: Hi, I have some problem with the data after perform Text to Column Function. Eg: I have this no = 123456789 I want to add "-" in between become 1-234-567-89 So i Use Text To Column Function to separate 9 digit no to 4 column. Then use this formula to combine: [ =Column1&"-"&Column2&"-"&Column3&"-"&Column4 ] Problem: when the no contain "0" in some location, like 102500301, the no in column2 become 25, column3 become 3 and column4 become 1. after combine will become 1-25-3-1 (Actual needed is 1-025-003-01) Can some one help to solve this problem (what code can be aded in VBA to solve this)? Regards YL Chuah -- ylchuah ------------------------------------------------------------------------ ylchuah's Profile: http://www.excelforum.com/member.php...o&userid=25341 View this thread: http://www.excelforum.com/showthread...hreadid=396025 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question on Text to Column Function
Hi, This does help me a lot. Thank you so much.... ;) YL Chuah -- ylchuah ------------------------------------------------------------------------ ylchuah's Profile: http://www.excelforum.com/member.php...o&userid=25341 View this thread: http://www.excelforum.com/showthread...hreadid=396025 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
inverse of the column function? i.e. input a number, output thecorresponding column text label | Excel Worksheet Functions | |||
Text function question. | Excel Worksheet Functions | |||
TEXT TO COLUMN FUNCTION | Excel Worksheet Functions | |||
Text To Column Question | Excel Discussion (Misc queries) | |||
TEXT function question | Excel Discussion (Misc queries) |