Re: $$Excel-Macros$$ Text separated with formula
So... you want to do it from within the macro.As Pankaj Sharma mentioned, there is a TextToColumns VBA method. Depending on how much control you want, you can also do this with an array. the split() function will divide up your value and put it into an array. So, you can use: mArray = split(range(A1).value,_) Then: Range(B1).value = mArray(0)Range(C1).value = mArray(1)Range(D1).value = mArray(2) Paul- “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley - From: Nur Hossain nur2...@gmail.com To: excel-macros excel-macros@googlegroups.com Sent: Wednesday, May 13, 2015 1:46 AM Subject: Re: $$Excel-Macros$$ Text separated with formula Hi Paul Schreiner, thanks for replay , I am using office2007, yes I agree with you that it is possible by doing text to column . but I don’t wanted to do any manual intervention as my whole projects having many sheets with full of formula andalso combination of macro. However I am doing it manually.. On Tue, May 12, 2015 at 11:21 PM, Paul Schreiner schreiner_p...@att.net wrote: What version of Excel are you using?Excel 2010 has Text to Columns on the Data tab. Paul- “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley - From: Nur Hossain nur2...@gmail.com To: excel-macros excel-macros@googlegroups.com Sent: Tuesday, May 12, 2015 12:17 PM Subject: $$Excel-Macros$$ Text separated with formula Dear all , I am having a text strings which is separated by underscore ( _ ) .please remember numeric number may vary more or less CELL A1= 10.20.193.49_255.255.255.240_1503 It need to be separated like below CELL wise form CELL A1 CELL B1= 10.20.193.49 CELL C1= 255.255.255.240 CELL D1= 1503 -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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
Re: $$Excel-Macros$$ Text separated with formula
use this Selection.TextToColumns Destination:=Range(B1), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, Other:=True, _ otherchar:=_, fieldinfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)),TrailingMinusNumbers:=True -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Text separated with formula
Hi, Check below link: http://www.get-digital-help.com/2014/12/02/split-text-across-columns/ Regards, Swapnil On Wed, May 13, 2015 at 11:16 AM, Nur Hossain nur2...@gmail.com wrote: Hi Paul Schreiner, thanks for replay , I am using office 2007, yes I agree with you that it is possible by doing text to column . but I don’t wanted to do any manual intervention as my whole projects having many sheets with full of formula and also combination of macro. However I am doing it manually.. On Tue, May 12, 2015 at 11:21 PM, Paul Schreiner schreiner_p...@att.net wrote: What version of Excel are you using? Excel 2010 has Text to Columns on the Data tab. *Paul* - *“Do all the good you can,By all the means you can,In all the ways you can,In all the places you can,At all the times you can,To all the people you can,As long as ever you can.” - John Wesley* - -- *From:* Nur Hossain nur2...@gmail.com *To:* excel-macros excel-macros@googlegroups.com *Sent:* Tuesday, May 12, 2015 12:17 PM *Subject:* $$Excel-Macros$$ Text separated with formula Dear all , I am having a text strings which is separated by underscore ( _ ) .please remember numeric number may vary more or less CELL A1= 10.20.193.49_255.255.255.240_1503 It need to be separated like below CELL wise form CELL A1 CELL B1= 10.20.193.49 CELL C1= 255.255.255.240 CELL D1= 1503 -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an
Re: $$Excel-Macros$$ Text separated with formula
Dear Paul Schreiner Pankaj Sharma Swapnil Palande : Thanks all of your help in this regard , On Wed, May 13, 2015 at 5:27 PM, Paul Schreiner schreiner_p...@att.net wrote: So... you want to do it from within the macro. As Pankaj Sharma mentioned, there is a TextToColumns VBA method. Depending on how much control you want, you can also do this with an array. the split() function will divide up your value and put it into an array. So, you can use: mArray = split(range(A1).value,_) Then: Range(B1).value = mArray(0) Range(C1).value = mArray(1) Range(D1).value = mArray(2) *Paul* - *“Do all the good you can,By all the means you can,In all the ways you can,In all the places you can,At all the times you can,To all the people you can,As long as ever you can.” - John Wesley* - -- *From:* Nur Hossain nur2...@gmail.com *To:* excel-macros excel-macros@googlegroups.com *Sent:* Wednesday, May 13, 2015 1:46 AM *Subject:* Re: $$Excel-Macros$$ Text separated with formula Hi Paul Schreiner, thanks for replay , I am using office 2007, yes I agree with you that it is possible by doing text to column . but I don’t wanted to do any manual intervention as my whole projects having many sheets with full of formula and also combination of macro. However I am doing it manually.. On Tue, May 12, 2015 at 11:21 PM, Paul Schreiner schreiner_p...@att.net wrote: What version of Excel are you using? Excel 2010 has Text to Columns on the Data tab. *Paul* - *“Do all the good you can,By all the means you can,In all the ways you can,In all the places you can,At all the times you can,To all the people you can,As long as ever you can.” - John Wesley* - -- *From:* Nur Hossain nur2...@gmail.com *To:* excel-macros excel-macros@googlegroups.com *Sent:* Tuesday, May 12, 2015 12:17 PM *Subject:* $$Excel-Macros$$ Text separated with formula Dear all , I am having a text strings which is separated by underscore ( _ ) .please remember numeric number may vary more or less CELL A1= 10.20.193.49_255.255.255.240_1503 It need to be separated like below CELL wise form CELL A1 CELL B1= 10.20.193.49 CELL C1= 255.255.255.240 CELL D1= 1503 -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do
Re: $$Excel-Macros$$ Text separated with formula
What version of Excel are you using?Excel 2010 has Text to Columns on the Data tab. Paul- “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley - From: Nur Hossain nur2...@gmail.com To: excel-macros excel-macros@googlegroups.com Sent: Tuesday, May 12, 2015 12:17 PM Subject: $$Excel-Macros$$ Text separated with formula Dear all , I am having a text strings which is separated by underscore ( _ ) .please remember numeric number may vary more or less CELL A1= 10.20.193.49_255.255.255.240_1503 It need to be separated like below CELL wise form CELL A1 CELL B1= 10.20.193.49 CELL C1= 255.255.255.240 CELL D1= 1503 -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Text separated with formula
Hi Paul Schreiner, thanks for replay , I am using office 2007, yes I agree with you that it is possible by doing text to column . but I don’t wanted to do any manual intervention as my whole projects having many sheets with full of formula and also combination of macro. However I am doing it manually.. On Tue, May 12, 2015 at 11:21 PM, Paul Schreiner schreiner_p...@att.net wrote: What version of Excel are you using? Excel 2010 has Text to Columns on the Data tab. *Paul* - *“Do all the good you can,By all the means you can,In all the ways you can,In all the places you can,At all the times you can,To all the people you can,As long as ever you can.” - John Wesley* - -- *From:* Nur Hossain nur2...@gmail.com *To:* excel-macros excel-macros@googlegroups.com *Sent:* Tuesday, May 12, 2015 12:17 PM *Subject:* $$Excel-Macros$$ Text separated with formula Dear all , I am having a text strings which is separated by underscore ( _ ) .please remember numeric number may vary more or less CELL A1= 10.20.193.49_255.255.255.240_1503 It need to be separated like below CELL wise form CELL A1 CELL B1= 10.20.193.49 CELL C1= 255.255.255.240 CELL D1= 1503 -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options,
