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 :eek:


--
ylchuah
------------------------------------------------------------------------
ylchuah's Profile: http://www.excelforum.com/member.php...o&userid=25341
View this thread: http://www.excelforum.com/showthread...hreadid=396025

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 :eek:


--
ylchuah
------------------------------------------------------------------------
ylchuah's Profile: http://www.excelforum.com/member.php...o&userid=25341
View this thread: http://www.excelforum.com/showthread...hreadid=396025

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