Or, even simpler without loop by changing mine to 

Sub TTC_SAS_Improved()
Columns("A").TextToColumns Destination:=Range("B1"), _
DataType:=xlFixedWidth, FieldInfo:= _
Array(Array(0, 1), Array(30, 1))

Columns("b:c").AutoFit
Columns("b:c").Replace Chr(32), ""
Columns("c").HorizontalAlignment = xlRight
End Sub

Don Guillett
SalesAid Software
dguille...@gmail.com

From: Sam Mathai Chacko 
Sent: Thursday, February 09, 2012 11:09 AM
To: excel-macros@googlegroups.com 
Subject: Re: $$Excel-Macros$$ split the value in two part

The macro splits it in to three columns.... 

a probable solution would be

Sub Spliter()
    
    Dim rng As Range
    Columns("A").TextToColumns Destination:=Range("B1"), _
                DataType:=2, OtherChar:=" ", _
                FieldInfo:=Array(Array(0, 2), Array(31, 1)), _
                TrailingMinusNumbers:=True
    With Range("B1:C" & Cells(Rows.Count, 1).End(xlUp).Row)
        For Each rng In .Cells
            rng.Value = Evaluate("=TRIM(SUBSTITUTE(" & rng.Address(0, 0) & 
",CHAR(160),"" ""))")
        Next rng
    End With
    
End Sub

Regards,

Sam Mathai Chacko



On Thu, Feb 9, 2012 at 10:25 PM, NOORAIN ANSARI <noorain.ans...@gmail.com> 
wrote:

  Good Solution SAM
  Thanks......


  On Thu, Feb 9, 2012 at 10:10 PM, Sam Mathai Chacko <samde...@gmail.com> wrote:

    Noorain, if you are using AB as your search criteria to split the cells, 
why do you need such a long formula

    Use

    Name 
=TRIM(SUBSTITUTE(SUBSTITUTE(A2,MID(A2,FIND("AB",A2),9),""),CHAR(160),""))

    Reg.No =SUBSTITUTE(MID(A2,FIND("AB",A2),9),CHAR(160),"")

    Regards,

    Sam Mathai Chacko 



    On Thu, Feb 9, 2012 at 8:43 PM, dguillett1 <dguille...@gmail.com> wrote:

      Try this
      Sub TTC_SAS()
      Columns("A").TextToColumns Destination:=Range("B1"), _
      DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), _
      Array(5, 1), Array(30, 1))
      Columns("C:D").AutoFit
      Columns("D").HorizontalAlignment = xlRight
      End Sub

      Don Guillett
      SalesAid Software
      dguille...@gmail.com

      From: Shaik Waheed 
      Sent: Thursday, February 09, 2012 1:44 AM
      To: excel-macros@googlegroups.com 
      Subject: Re: $$Excel-Macros$$ split the value in two part

      Hi Sanjib,

      In your sample data u can do this by clicking to 'Data' tab in ur excel 
sheet--->Text To Colums---> Fixed width-->Next---> Set the scale line as per ur 
requirement---> finish

      Do the necessary formatting as per ur requirement.
      Find the attached sheet for ur review.


      On Thu, Feb 9, 2012 at 12:49 PM, Sanjib Chatterjee 
<chatterjee.kolk...@gmail.com> wrote:



        Dear Sir / Madam,

        Please see the attached excel sheet.  I like to split the name  in one 
cell (i.e. in B) and registration in another cell (i.e in C)

        Please help me.

        Thank you in Advance

        Regards,

        Sanjib
        -- 
        -
        -- 
        FORUM RULES (986+ members already BANNED for violation)
         
        1) Use concise, accurate thread titles. Poor thread titles, like Please 
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will 
not get quick attention or may not be answered.
         
        2) Don't post a question in the thread of another member.
         
        3) Don't post questions regarding breaking or bypassing any security 
measure.
         
        4) Acknowledge the responses you receive, good or bad.
         
        5) Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 
         
        NOTE : Don't ever post personal or confidential data in a workbook. 
Forum owners and members are not responsible for any loss.
         
        
------------------------------------------------------------------------------------------------------
        To post to this group, send email to excel-macros@googlegroups.com


      -- 
      FORUM RULES (986+ members already BANNED for violation)
       
      1) Use concise, accurate thread titles. Poor thread titles, like Please 
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will 
not get quick attention or may not be answered.
       
      2) Don't post a question in the thread of another member.
       
      3) Don't post questions regarding breaking or bypassing any security 
measure.
       
      4) Acknowledge the responses you receive, good or bad.
       
      5) Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 
       
      NOTE : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.
       
      
------------------------------------------------------------------------------------------------------
      To post to this group, send email to excel-macros@googlegroups.com

      -- 
      FORUM RULES (986+ members already BANNED for violation)
       
      1) Use concise, accurate thread titles. Poor thread titles, like Please 
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will 
not get quick attention or may not be answered.
       
      2) Don't post a question in the thread of another member.
       
      3) Don't post questions regarding breaking or bypassing any security 
measure.
       
      4) Acknowledge the responses you receive, good or bad.
       
      5) Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 
       
      NOTE : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.
       
      
------------------------------------------------------------------------------------------------------
      To post to this group, send email to excel-macros@googlegroups.com





    -- 
    Sam Mathai Chacko 

    -- 
    FORUM RULES (986+ members already BANNED for violation)
     
    1) Use concise, accurate thread titles. Poor thread titles, like Please 
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will 
not get quick attention or may not be answered.
     
    2) Don't post a question in the thread of another member.
     
    3) Don't post questions regarding breaking or bypassing any security 
measure.
     
    4) Acknowledge the responses you receive, good or bad.
     
    5) Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 
     
    NOTE : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.
     
    
------------------------------------------------------------------------------------------------------
    To post to this group, send email to excel-macros@googlegroups.com




  -- 

  Thanks & regards,
  Noorain Ansari
  http://noorainansari.com/
  http://excelmacroworld.blogspot.com/



  -- 
  FORUM RULES (986+ members already BANNED for violation)
   
  1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
   
  2) Don't post a question in the thread of another member.
   
  3) Don't post questions regarding breaking or bypassing any security measure.
   
  4) Acknowledge the responses you receive, good or bad.
   
  5) Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 
   
  NOTE : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.
   
  
------------------------------------------------------------------------------------------------------
  To post to this group, send email to excel-macros@googlegroups.com




-- 
Sam Mathai Chacko
-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.
 
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com

Reply via email to