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