Re: $$Excel-Macros$$ Re: how to remove space between numbers only.
Beginning to like it in this group. It works but would have preferred if text was returned also Francis Mukobi Frank Web Hosts Your affordable quality web hosts http://frankhost.net Phone: +256 752 954 723 For every domain you purchase from us, we host your website for the first 3 months for FREE! Manoj Kumar manoj.s...@gmail.com wrote: Thanks Anoop. Solved My Problem. but it would be nice if formula also return the name along with phone number. On Wednesday, 2 January 2013 13:17:35 UTC+5:30, Enrique Martin wrote: Paste the following code in the module. this will extract all the numbers removing spaces. Please see the example in attached file. Function RemoveSpaces(str As String) As String Dim myLen As Long Dim myChar As String Dim myWrd As String Dim iCount As Integer Dim myGet As String myLen = Len(str) myWrd = str For iCount = 1 To myLen myChar = Mid$(myWrd, iCount, 1) If IsNumeric(myChar) Then myGet = myGet myChar End If Debug.Print myChar Next iCount RemoveSpaces = myGet End Function Regards, Anoop On Wed, Jan 2, 2013 at 1:01 PM, Prince prince...@gmail.com javascript:wrote: In this case we need to have a fomula that can count the existence of number in the text so in that case we need to modify this formula. that i let u know how. Regards Prince On Wednesday, January 2, 2013 12:54:34 PM UTC+5:30, Francis Mukobi wrote: What if it is a list of phone numbers and these numbers have different lengths? I mean some phone numbers are from India (10 digits ) and some numbers are from say Indonesia (14 digits ) etc. How do I remove spaces from such a list of numbers? Over to you Prince. Francis Mukobi Frank Web Hosts Your affordable quality web hosts http://frankhost.net For every domain purchased from us, we host you for the first 3 months FREE! Prince prince...@gmail.com wrote: Hi Francis, This formula cut the string from first charactor up the number of numeric value inside that text i mean i have assumed that numeric value is a mobile number and every mobile number contains 10 digits in india so i cut the last 12 digit including space and concat that with first string that i got from left. Regards Prince On Wednesday, January 2, 2013 12:00:56 PM UTC+5:30, Francis Mukobi wrote: Could you please elaborate on the formula you have given, Prince? Francis Mukobi Frank Web Hosts Your affordable quality web hosts http://frankhost.net Manoj Kumar manoj...@gmail.com javascript: wrote: there is some text at end also. Sorry for inconvenience. On Wed, Jan 2, 2013 at 11:20 AM, Prince prince...@gmail.com**javascript: wrote: Hi Manoj, use this this may help you incase if the Number is any Mobile number: =MID(A1,1,LEN(A1)-12)**SUBSTITUTE(RIGHT(A1,12), ,) regards Prince On Wednesday, January 2, 2013 11:04:37 AM UTC+5:30, Manoj Kumar wrote: Hi, Is there any way out for removing spaces between numbers only. Spaces between the text should be removed. For example. Manoj Kumar 9015 408 078 Manoj Kumar 9015418078 The text at the front is not always fixed. It can contain any number text and spaces. Regards, Manoj -- Join official Facebook page of this forum @ https://www.facebook.com/**discussexcelhttps://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 post to this group, send email to excel-...@googlegroups.com** javascript: . To unsubscribe from this group, send email to excel-macros...@googlegroups.**com javascript:. Visit this group at http://groups.google.com/** group/excel-macros?hl=enhttp://groups.google.com/group/excel-macros?hl=en . -- Regards, Manoj -- Join official Facebook page of this forum @ https://www.facebook.com/**discussexcelhttps://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code
Re: $$Excel-Macros$$ Re: how to remove space between numbers only.
Wow!! Thank you very much. Problem solved.. On Wednesday, 2 January 2013 14:10:36 UTC+5:30, Enrique Martin wrote: Replace the code with following one. Remember you are concatenating text and Numeric. Find attached the updated one Function RemoveSpaces(str As String) As String Dim myLen As Long Dim myChar As String Dim myWrd As String Dim iCount As Integer Dim myGet As String Dim myGet1 As String myLen = Len(str) myWrd = str For iCount = 1 To myLen myChar = Mid$(myWrd, iCount, 1) If IsNumeric(myChar) Then myGet = myGet myChar Else myGet1 = myGet1 myChar End If Next iCount RemoveSpaces = myGet1 Space(1) myGet End Function On Wed, Jan 2, 2013 at 1:45 PM, Francis Mukobi franci...@gmail.comjavascript: wrote: Beginning to like it in this group. It works but would have preferred if text was returned also Francis Mukobi Frank Web Hosts Your affordable quality web hosts http://frankhost.net Phone: +256 752 954 723 For every domain you purchase from us, we host your website for the first 3 months for FREE! Manoj Kumar manoj...@gmail.com javascript: wrote: Thanks Anoop. Solved My Problem. but it would be nice if formula also return the name along with phone number. On Wednesday, 2 January 2013 13:17:35 UTC+5:30, Enrique Martin wrote: Paste the following code in the module. this will extract all the numbers removing spaces. Please see the example in attached file. Function RemoveSpaces(str As String) As String Dim myLen As Long Dim myChar As String Dim myWrd As String Dim iCount As Integer Dim myGet As String myLen = Len(str) myWrd = str For iCount = 1 To myLen myChar = Mid$(myWrd, iCount, 1) If IsNumeric(myChar) Then myGet = myGet myChar End If Debug.Print myChar Next iCount RemoveSpaces = myGet End Function Regards, Anoop On Wed, Jan 2, 2013 at 1:01 PM, Prince prince...@gmail.comjavascript:wrote: In this case we need to have a fomula that can count the existence of number in the text so in that case we need to modify this formula. that i let u know how. Regards Prince On Wednesday, January 2, 2013 12:54:34 PM UTC+5:30, Francis Mukobi wrote: What if it is a list of phone numbers and these numbers have different lengths? I mean some phone numbers are from India (10 digits ) and some numbers are from say Indonesia (14 digits ) etc. How do I remove spaces from such a list of numbers? Over to you Prince. Francis Mukobi Frank Web Hosts Your affordable quality web hosts http://frankhost.net For every domain purchased from us, we host you for the first 3 months FREE! Prince prince...@gmail.com wrote: Hi Francis, This formula cut the string from first charactor up the number of numeric value inside that text i mean i have assumed that numeric value is a mobile number and every mobile number contains 10 digits in india so i cut the last 12 digit including space and concat that with first string that i got from left. Regards Prince On Wednesday, January 2, 2013 12:00:56 PM UTC+5:30, Francis Mukobi wrote: Could you please elaborate on the formula you have given, Prince? Francis Mukobi Frank Web Hosts Your affordable quality web hosts http://frankhost.net Manoj Kumar manoj...@gmail.com javascript: wrote: there is some text at end also. Sorry for inconvenience. On Wed, Jan 2, 2013 at 11:20 AM, Prince prince...@gmail.com **javascript: wrote: Hi Manoj, use this this may help you incase if the Number is any Mobile number: =MID(A1,1,LEN(A1)-12)**SUBSTITUTE(RIGHT(A1,12), ,) regards Prince On Wednesday, January 2, 2013 11:04:37 AM UTC+5:30, Manoj Kumar wrote: Hi, Is there any way out for removing spaces between numbers only. Spaces between the text should be removed. For example. Manoj Kumar 9015 408 078 Manoj Kumar 9015418078 The text at the front is not always fixed. It can contain any number text and spaces. Regards, Manoj -- Join official Facebook page of this forum @ https://www.facebook.com/**discussexcel 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
Re: $$Excel-Macros$$ Re: how to remove space between numbers only.
Hi Priyanka, You can refer some good e-books. Why don't you try this on bookboon.com. As a beginner you should go with Excel Bible, this will teach you not only excel basics but also VBA at initial stage. Regards, Anoop Sr. Developer On Wed, Jan 2, 2013 at 2:18 PM, Manoj Kumar manoj.s...@gmail.com wrote: Wow!! Thank you very much. Problem solved.. On Wednesday, 2 January 2013 14:10:36 UTC+5:30, Enrique Martin wrote: Replace the code with following one. Remember you are concatenating text and Numeric. Find attached the updated one Function RemoveSpaces(str As String) As String Dim myLen As Long Dim myChar As String Dim myWrd As String Dim iCount As Integer Dim myGet As String Dim myGet1 As String myLen = Len(str) myWrd = str For iCount = 1 To myLen myChar = Mid$(myWrd, iCount, 1) If IsNumeric(myChar) Then myGet = myGet myChar Else myGet1 = myGet1 myChar End If Next iCount RemoveSpaces = myGet1 Space(1) myGet End Function On Wed, Jan 2, 2013 at 1:45 PM, Francis Mukobi franci...@gmail.comwrote: Beginning to like it in this group. It works but would have preferred if text was returned also Francis Mukobi Frank Web Hosts Your affordable quality web hosts http://frankhost.net Phone: +256 752 954 723 For every domain you purchase from us, we host your website for the first 3 months for FREE! Manoj Kumar manoj...@gmail.com wrote: Thanks Anoop. Solved My Problem. but it would be nice if formula also return the name along with phone number. On Wednesday, 2 January 2013 13:17:35 UTC+5:30, Enrique Martin wrote: Paste the following code in the module. this will extract all the numbers removing spaces. Please see the example in attached file. Function RemoveSpaces(str As String) As String Dim myLen As Long Dim myChar As String Dim myWrd As String Dim iCount As Integer Dim myGet As String myLen = Len(str) myWrd = str For iCount = 1 To myLen myChar = Mid$(myWrd, iCount, 1) If IsNumeric(myChar) Then myGet = myGet myChar End If Debug.Print myChar Next iCount RemoveSpaces = myGet End Function Regards, Anoop On Wed, Jan 2, 2013 at 1:01 PM, Prince prince...@gmail.comjavascript:wrote: In this case we need to have a fomula that can count the existence of number in the text so in that case we need to modify this formula. that i let u know how. Regards Prince On Wednesday, January 2, 2013 12:54:34 PM UTC+5:30, Francis Mukobi wrote: What if it is a list of phone numbers and these numbers have different lengths? I mean some phone numbers are from India (10 digits ) and some numbers are from say Indonesia (14 digits ) etc. How do I remove spaces from such a list of numbers? Over to you Prince. Francis Mukobi Frank Web Hosts Your affordable quality web hosts http://frankhost.net For every domain purchased from us, we host you for the first 3 months FREE! Prince prince...@gmail.com wrote: Hi Francis, This formula cut the string from first charactor up the number of numeric value inside that text i mean i have assumed that numeric value is a mobile number and every mobile number contains 10 digits in india so i cut the last 12 digit including space and concat that with first string that i got from left. Regards Prince On Wednesday, January 2, 2013 12:00:56 PM UTC+5:30, Francis Mukobi wrote: Could you please elaborate on the formula you have given, Prince? Francis Mukobi Frank Web Hosts Your affordable quality web hosts http://frankhost.net Manoj Kumar manoj...@gmail.com javascript: wrote: there is some text at end also. Sorry for inconvenience. On Wed, Jan 2, 2013 at 11:20 AM, Prince prince...@gmail.com** **javascript: wrote: Hi Manoj, use this this may help you incase if the Number is any Mobile number: =MID(A1,1,LEN(A1)-12)SUBSTITUTE(RIGHT(A1,12), ,) regards Prince On Wednesday, January 2, 2013 11:04:37 AM UTC+5:30, Manoj Kumar wrote: Hi, Is there any way out for removing spaces between numbers only. Spaces between the text should be removed. For example. Manoj Kumar 9015 408 078 Manoj Kumar 9015418078 The text at the front is not always fixed. It can contain any number text and spaces. Regards, Manoj -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcelhttps://www.facebook.com/**discussexcel
Re: $$Excel-Macros$$ Re: how to remove space between numbers only.
Thanks Anoop Will come back to u after reading the books,if I don't get anything shall I ask u for better clarification Sent from my BlackBerry® smartphone from !DEA -Original Message- From: Anoop K Sharma aks.sharm...@gmail.com Sender: excel-macros@googlegroups.com Date: Wed, 2 Jan 2013 14:26:36 To: excel-macros@googlegroups.com Reply-To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Re: how to remove space between numbers only. Hi Priyanka, You can refer some good e-books. Why don't you try this on bookboon.com. As a beginner you should go with Excel Bible, this will teach you not only excel basics but also VBA at initial stage. Regards, Anoop Sr. Developer On Wed, Jan 2, 2013 at 2:18 PM, Manoj Kumar manoj.s...@gmail.com wrote: Wow!! Thank you very much. Problem solved.. On Wednesday, 2 January 2013 14:10:36 UTC+5:30, Enrique Martin wrote: Replace the code with following one. Remember you are concatenating text and Numeric. Find attached the updated one Function RemoveSpaces(str As String) As String Dim myLen As Long Dim myChar As String Dim myWrd As String Dim iCount As Integer Dim myGet As String Dim myGet1 As String myLen = Len(str) myWrd = str For iCount = 1 To myLen myChar = Mid$(myWrd, iCount, 1) If IsNumeric(myChar) Then myGet = myGet myChar Else myGet1 = myGet1 myChar End If Next iCount RemoveSpaces = myGet1 Space(1) myGet End Function On Wed, Jan 2, 2013 at 1:45 PM, Francis Mukobi franci...@gmail.comwrote: Beginning to like it in this group. It works but would have preferred if text was returned also Francis Mukobi Frank Web Hosts Your affordable quality web hosts http://frankhost.net Phone: +256 752 954 723 For every domain you purchase from us, we host your website for the first 3 months for FREE! Manoj Kumar manoj...@gmail.com wrote: Thanks Anoop. Solved My Problem. but it would be nice if formula also return the name along with phone number. On Wednesday, 2 January 2013 13:17:35 UTC+5:30, Enrique Martin wrote: Paste the following code in the module. this will extract all the numbers removing spaces. Please see the example in attached file. Function RemoveSpaces(str As String) As String Dim myLen As Long Dim myChar As String Dim myWrd As String Dim iCount As Integer Dim myGet As String myLen = Len(str) myWrd = str For iCount = 1 To myLen myChar = Mid$(myWrd, iCount, 1) If IsNumeric(myChar) Then myGet = myGet myChar End If Debug.Print myChar Next iCount RemoveSpaces = myGet End Function Regards, Anoop On Wed, Jan 2, 2013 at 1:01 PM, Prince prince...@gmail.comjavascript:wrote: In this case we need to have a fomula that can count the existence of number in the text so in that case we need to modify this formula. that i let u know how. Regards Prince On Wednesday, January 2, 2013 12:54:34 PM UTC+5:30, Francis Mukobi wrote: What if it is a list of phone numbers and these numbers have different lengths? I mean some phone numbers are from India (10 digits ) and some numbers are from say Indonesia (14 digits ) etc. How do I remove spaces from such a list of numbers? Over to you Prince. Francis Mukobi Frank Web Hosts Your affordable quality web hosts http://frankhost.net For every domain purchased from us, we host you for the first 3 months FREE! Prince prince...@gmail.com wrote: Hi Francis, This formula cut the string from first charactor up the number of numeric value inside that text i mean i have assumed that numeric value is a mobile number and every mobile number contains 10 digits in india so i cut the last 12 digit including space and concat that with first string that i got from left. Regards Prince On Wednesday, January 2, 2013 12:00:56 PM UTC+5:30, Francis Mukobi wrote: Could you please elaborate on the formula you have given, Prince? Francis Mukobi Frank Web Hosts Your affordable quality web hosts http://frankhost.net Manoj Kumar manoj...@gmail.com javascript: wrote: there is some text at end also. Sorry for inconvenience. On Wed, Jan 2, 2013 at 11:20 AM, Prince prince...@gmail.com** **javascript: wrote: Hi Manoj, use this this may help you incase if the Number is any Mobile number: =MID(A1,1,LEN(A1)-12)SUBSTITUTE(RIGHT(A1,12), ,) regards Prince On Wednesday, January 2, 2013 11:04:37 AM UTC+5:30, Manoj Kumar wrote: Hi, Is there any way out for removing spaces between numbers only. Spaces between
Re: $$Excel-Macros$$ Re: how to remove space between numbers only.
Hi, Sorry for the long formula if sombody found somthing in short please let me know ;) If value is in cell A1 paste below code on cell A2 with Ctrl + Shift + Enter =REPLACE(A1,MATCH(1,N(ISNUMBER(VALUE(MID(A1,ROW(INDIRECT(1:LEN(A1))),4,0)+1,MAX(N(ISNUMBER(VALUE(MID(A1,ROW(INDIRECT(1:LEN(A1))),1*ROW(INDIRECT(1:LEN(A1-MATCH(1,N(ISNUMBER(VALUE(MID(A1,ROW(INDIRECT(1:LEN(A1))),4,0),SUBSTITUTE(MID(A1,MATCH(1,N(ISNUMBER(VALUE(MID(A1,ROW(INDIRECT(1:LEN(A1))),4,0),MAX(N(ISNUMBER(VALUE(MID(A1,ROW(INDIRECT(1:LEN(A1))),1*ROW(INDIRECT(1:LEN(A1-MATCH(1,N(ISNUMBER(VALUE(MID(A1,ROW(INDIRECT(1:LEN(A1))),4,0)+1), ,)) Regards, Lalit Mohan On Wednesday, 2 January 2013 13:25:54 UTC+5:30, Manoj Kumar wrote: Thanks Anoop. Solved My Problem. but it would be nice if formula also return the name along with phone number. On Wednesday, 2 January 2013 13:17:35 UTC+5:30, Enrique Martin wrote: Paste the following code in the module. this will extract all the numbers removing spaces. Please see the example in attached file. Function RemoveSpaces(str As String) As String Dim myLen As Long Dim myChar As String Dim myWrd As String Dim iCount As Integer Dim myGet As String myLen = Len(str) myWrd = str For iCount = 1 To myLen myChar = Mid$(myWrd, iCount, 1) If IsNumeric(myChar) Then myGet = myGet myChar End If Debug.Print myChar Next iCount RemoveSpaces = myGet End Function Regards, Anoop On Wed, Jan 2, 2013 at 1:01 PM, Prince prince...@gmail.com wrote: In this case we need to have a fomula that can count the existence of number in the text so in that case we need to modify this formula. that i let u know how. Regards Prince On Wednesday, January 2, 2013 12:54:34 PM UTC+5:30, Francis Mukobi wrote: What if it is a list of phone numbers and these numbers have different lengths? I mean some phone numbers are from India (10 digits ) and some numbers are from say Indonesia (14 digits ) etc. How do I remove spaces from such a list of numbers? Over to you Prince. Francis Mukobi Frank Web Hosts Your affordable quality web hosts http://frankhost.net For every domain purchased from us, we host you for the first 3 months FREE! Prince prince...@gmail.com wrote: Hi Francis, This formula cut the string from first charactor up the number of numeric value inside that text i mean i have assumed that numeric value is a mobile number and every mobile number contains 10 digits in india so i cut the last 12 digit including space and concat that with first string that i got from left. Regards Prince On Wednesday, January 2, 2013 12:00:56 PM UTC+5:30, Francis Mukobi wrote: Could you please elaborate on the formula you have given, Prince? Francis Mukobi Frank Web Hosts Your affordable quality web hosts http://frankhost.net Manoj Kumar manoj...@gmail.com javascript: wrote: there is some text at end also. Sorry for inconvenience. On Wed, Jan 2, 2013 at 11:20 AM, Prince prince...@gmail.com**javascript: wrote: Hi Manoj, use this this may help you incase if the Number is any Mobile number: =MID(A1,1,LEN(A1)-12)**SUBSTITUTE(RIGHT(A1,12), ,) regards Prince On Wednesday, January 2, 2013 11:04:37 AM UTC+5:30, Manoj Kumar wrote: Hi, Is there any way out for removing spaces between numbers only. Spaces between the text should be removed. For example. Manoj Kumar 9015 408 078 Manoj Kumar 9015418078 The text at the front is not always fixed. It can contain any number text and spaces. Regards, Manoj -- Join official Facebook page of this forum @ https://www.facebook.com/**discussexcelhttps://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 post to this group, send email to excel-...@googlegroups.com* *javascript: . To unsubscribe from this group, send email to excel-macros...@googlegroups.**com javascript:. Visit this group at http://groups.google.com/**
Fwd: $$Excel-Macros$$ Re: how to remove space between numbers only.
It's absolutely working fine. Pravin Gunjal -- Forwarded message -- From: Prince prince141...@gmail.com Date: Wed, Jan 2, 2013 at 11:20 AM Subject: $$Excel-Macros$$ Re: how to remove space between numbers only. To: excel-macros@googlegroups.com Hi Manoj, use this this may help you incase if the Number is any Mobile number: =MID(A1,1,LEN(A1)-12)SUBSTITUTE(RIGHT(A1,12), ,) regards Prince On Wednesday, January 2, 2013 11:04:37 AM UTC+5:30, Manoj Kumar wrote: Hi, Is there any way out for removing spaces between numbers only. Spaces between the text should be removed. For example. Manoj Kumar 9015 408 078 Manoj Kumar 9015418078 The text at the front is not always fixed. It can contain any number text and spaces. Regards, Manoj -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
Re: $$Excel-Macros$$ Re: how to remove space between numbers only.
=LEFT(A1,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A1),))-1)SUBSTITUTE(MID(A1,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A1),)),255),
,)
this should work with CSE
On Wed, Jan 2, 2013 at 2:55 PM, Pravin Gunjal isk1...@gmail.com wrote:
It's absolutely working fine.
Pravin Gunjal
-- Forwarded message --
From: Prince prince141...@gmail.com
Date: Wed, Jan 2, 2013 at 11:20 AM
Subject: $$Excel-Macros$$ Re: how to remove space between numbers only.
To: excel-macros@googlegroups.com
Hi Manoj,
use this this may help you incase if the Number is any Mobile number:
=MID(A1,1,LEN(A1)-12)SUBSTITUTE(RIGHT(A1,12), ,)
regards
Prince
On Wednesday, January 2, 2013 11:04:37 AM UTC+5:30, Manoj Kumar wrote:
Hi,
Is there any way out for removing spaces between numbers only. Spaces
between the text should be removed.
For example.
Manoj Kumar 9015 408 078 Manoj Kumar 9015418078
The text at the front is not always fixed. It can contain any number text
and spaces.
Regards,
Manoj
--
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 post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
--
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 post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
--
*Great day,*
*viper*
--
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 post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
Re: $$Excel-Macros$$ Re: how to remove space between numbers only.
Nice formula viper but the last space is missing.
On Wednesday, 2 January 2013 15:07:20 UTC+5:30, §»VIPER«§ wrote:
=LEFT(A1,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A1),))-1)SUBSTITUTE(MID(A1,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A1),)),255),
,)
this should work with CSE
On Wed, Jan 2, 2013 at 2:55 PM, Pravin Gunjal isk...@gmail.comjavascript:
wrote:
It's absolutely working fine.
Pravin Gunjal
-- Forwarded message --
From: Prince prince...@gmail.com javascript:
Date: Wed, Jan 2, 2013 at 11:20 AM
Subject: $$Excel-Macros$$ Re: how to remove space between numbers only.
To: excel-...@googlegroups.com javascript:
Hi Manoj,
use this this may help you incase if the Number is any Mobile number:
=MID(A1,1,LEN(A1)-12)SUBSTITUTE(RIGHT(A1,12), ,)
regards
Prince
On Wednesday, January 2, 2013 11:04:37 AM UTC+5:30, Manoj Kumar wrote:
Hi,
Is there any way out for removing spaces between numbers only. Spaces
between the text should be removed.
For example.
Manoj Kumar 9015 408 078 Manoj Kumar 9015418078
The text at the front is not always fixed. It can contain any number
text and spaces.
Regards,
Manoj
--
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 post to this group, send email to excel-...@googlegroups.comjavascript:
.
To unsubscribe from this group, send email to
excel-macros...@googlegroups.com javascript:.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
--
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 post to this group, send email to excel-...@googlegroups.comjavascript:
.
To unsubscribe from this group, send email to
excel-macros...@googlegroups.com javascript:.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
--
*Great day,*
*viper*
--
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 post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
Re: $$Excel-Macros$$ Excel Fun Video *
Love this why did he choose the choosen one level On Jan 1, 2013 10:48 AM, amar takale amartak...@gmail.com wrote: [image: Boxbe] https://www.boxbe.com/overview This message is eligible for Automatic Cleanup! (amartak...@gmail.com) Add cleanup rulehttps://www.boxbe.com/popup?url=https%3A%2F%2Fwww.boxbe.com%2Fcleanup%3Ftoken%3Dk58ARKPERNKnhgShasnHe7Ad4ebzbhGgLqb71gF5JevKX0qcyD8Mxq6NWDnFg28Q385HjawvsVecPKYgtVxs0Nk6ubjLRhMUWLVj7QkLijs5wwNZXgd6Md1FYhd88zkamXdrq3gUAWY%253D%26key%3D5If0u8d2Bz9ED2rWA2BLJbApMN9w9BlMwJ%252BygqZeC%252F0%253Dtc=13253185260_22126120| More infohttp://blog.boxbe.com/general/boxbe-automatic-cleanup?tc=13253185260_22126120 Dear my all Excel Group Members, I found Excel fun Video on Internet while searching Excel Solution I like very much.I hope you all like it fun Video.So I attached this Excel.Enjoy every excel users. Regards Amar -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
$$Excel-Macros$$ Re: Please unsuscribe
Please unsubscribe On Wed, Jan 2, 2013 at 10:25 AM, Praveen Golash praveen.gol...@gmail.comwrote: Please do the needful. -- Regards, Praveen Golash -- Regards, Praveen Golash KPMG Advisory Services Pvt. Ltd. Mobile: +91- 9711302259 -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
Re: $$Excel-Macros$$ Happy b'day Noorain Ansari
Thanks to all of you... On Wed, Jan 2, 2013 at 12:44 PM, resp...@gmail.com wrote: Happy Birthday big bro Sent from my BlackBerry® smartphone from Airtel Ghana -Original Message- From: koul.ash...@gmail.com Sender: excel-macros@googlegroups.com Date: Tue, 1 Jan 2013 18:43:59 To: excel-macros@googlegroups.com Reply-To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Happy b'day Noorain Ansari Happy b'day Noorain Ansari Regards Ashish Koul Sent on my BlackBerry® from Vodafone -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- With Regards, Noorain Ansari http:// http://www.noorainansari.comnoorainansari.comhttp://www.noorainansari.com http:// http://www.excelvbaclinic.blogspot.comexcelvbaclinic.comhttp://www.excelvbaclinic.blogspot.comhttp://accesssqclinic.blogspot.in/ -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
Fwd: $$Excel-Macros$$ Re: Please unsuscribe
*To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com* -- Forwarded message -- From: Praveen Golash praveen.gol...@gmail.com Date: Wed, Jan 2, 2013 at 5:36 PM Subject: $$Excel-Macros$$ Re: Please unsuscribe To: excel-macros@googlegroups.com To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
$$Excel-Macros$$ How to find special characters in a cell
In excel 2012 I'm using the name typed in a cell as a part of the filename when saved via a macro. Because the following characters /\*?| are not allowed in a filename I want to do a check in the cell. The idea is to put a warning in a free cell that the name isn't allowed when it contains one or more of the forbidden characters. I used the =IF function in combination with =SEARCH but I don't know how to use the formulas when I want to check on * ? or Can somebody help me with that? Thanks. Danny -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
$$Excel-Macros$$ Re: Introduce Yourself !!
Hello All, My name is Danny Mariens and I work as a crewplanner in Belgium. The company I work for is PRG with offices around the world. I'm working in excel all day because this is our only planningtool. -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
Re: $$Excel-Macros$$ How to find special characters in a cell
can you share workbook? On Wed, Jan 2, 2013 at 8:34 PM, dannymari...@gmail.com wrote: In excel 2012 I'm using the name typed in a cell as a part of the filename when saved via a macro. Because the following characters /\*?| are not allowed in a filename I want to do a check in the cell. The idea is to put a warning in a free cell that the name isn't allowed when it contains one or more of the forbidden characters. I used the =IF function in combination with =SEARCH but I don't know how to use the formulas when I want to check on * ? or Can somebody help me with that? Thanks. Danny -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- Regards, Anoop Sr. Developer -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
Re: $$Excel-Macros$$ How to find special characters in a cell
Hi Danny, May be this code will work for you: *Private Sub Worksheet_SelectionChange(ByVal Target As Range)* * * *If Target.Resize(1, 1) = ThisWorkbook.Worksheets(JIBase).Range(G4) Then* *If Len(Trim(Target.Resize(1, 1).Value)) 0 Then* *If Not CreateFile(Target.Resize(1, 1)) Then* *MsgBox Name is not valid as it contains some special character., vbCritical, File name is not valid.* *Target.Value = vbNullString* *End If* *End If* *End If* * * *End Sub* * * *Function CreateFile(ByVal strFileName As String) As Boolean* ** *Dim objFSO As Object* *Dim objTFC As Object* *Dim strFilePath As String* ** *strFilePath = Environ(temp) Application.PathSeparator strFileName .txt* *Set objFSO = CreateObject(Scripting.FileSystemObject)* *On Error Resume Next* *Set objTFC = Nothing* *Set objTFC = objFSO.CreateTextFile(strFilePath, True)* *On Error GoTo 0: Err.Clear* ** *If Not objTFC Is Nothing Then* *CreateFile = True* *On Error Resume Next* *Kill strFilePath* *On Error GoTo 0: Err.Clear* *Else* *CreateFile = False* *End If* ** *Set objFSO = Nothing* *Set objTFC = Nothing* *strFilePath = vbNullString* ** *End Function* Regards, Lalit Mohan On Wednesday, 2 January 2013 21:12:55 UTC+5:30, Danny Mariens wrote: I send workbook in annex. Protection is on because my collegues use the file with protection on. I removed password so you can easily switch it on or off. The cell containing the info that I use to save is G4 on JIBase. 2013/1/2 Anoop K Sharma aks.sh...@gmail.com javascript: can you share workbook? On Wed, Jan 2, 2013 at 8:34 PM, dannym...@gmail.com javascript:wrote: In excel 2012 I'm using the name typed in a cell as a part of the filename when saved via a macro. Because the following characters /\*?| are not allowed in a filename I want to do a check in the cell. The idea is to put a warning in a free cell that the name isn't allowed when it contains one or more of the forbidden characters. I used the =IF function in combination with =SEARCH but I don't know how to use the formulas when I want to check on * ? or Can somebody help me with that? Thanks. Danny -- 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 post to this group, send email to excel-...@googlegroups.comjavascript: . To unsubscribe from this group, send email to excel-macros...@googlegroups.com javascript:. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- Regards, Anoop Sr. Developer -- 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 post to this group, send email to excel-...@googlegroups.comjavascript: . To unsubscribe from this group, send email to excel-macros...@googlegroups.com javascript:. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- 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
$$Excel-Macros$$ Re: Macro to create attendance Sheet for each Employee
Hello Lalit, I tried the macro and it works perfectly on the sample data I uploaded in my first post. But very very sorry, I missed to say that Col A has date/Time ad Col B has the Name and not the other way round. Can the code be amended accordingly please. However, if I run your code by interchanging Col A and Col B; I get an error as follows: Run time error '9' - Subscript out of range and it highlights the following line in the code *Worksheets(strSampleFileName).Visible = True* Can you please rectify this problem and thanks a million for all your time and help sorry for my error. Regards Rashid Khan I On Tuesday, January 1, 2013 8:07:35 PM UTC+4, Lalit Mohan Pandey wrote: Hi Rashid, PFA. Regards, Lalit Mohan On Tuesday, 1 January 2013 15:16:42 UTC+5:30, Lalit Mohan Pandey wrote: Hi Rashid, Sorry for that i will send you the file by today evening itself Regards, Lalit Mohan On Tuesday, 1 January 2013 13:37:21 UTC+5:30, prkhan56 wrote: Hello Lalit I cannot see the complete code. If I go and check the original and paste the code I get lot of unwanted characters in the code If you cannot send me in a file then can you please send the code to me as a text file. thanks for your time and help Rashid Khan On Monday, December 31, 2012 3:09:26 PM UTC+4, Lalit Mohan Pandey wrote: Hi Rashid, Sorry i can't upload file so please follow the given steps: *Step 1:- First create a sample sheet for output report with all the formatting and formula and please make sure it should be for 31 rows as * *max day in a month is 31.* *Step 2:- Press Alt + F11 and Alt + I + M and paste below code * * * *Option Explicit Sub GetEmployeeAttendance() Dim wksSht As Worksheet Dim wksReport As Worksheet Dim rngRangeAs Range Dim varData() As Variant Dim varName() As Variant Dim varDate() As Variant Dim varFinal() As Variant Dim lngLoopName As Long Dim lngLoopDate As Long Dim lngCountAs Long Const strFormulaAs String = =A1 | TEXT(B1,m/d/) Const strTmpSht As String = Temp_Sht Const strDataStartCell As String = A1 Const strFinalDataStartCell As String = D8 Const strReportMonthCellAs String = C2 Const strEmpNameCellAs String = C5 Const strSampleFileName As String = Sample ReDim varData(0) With ThisWorkbook.Worksheets(Master) Set rngRange = .Range(strDataStartCell).Resize(.Cells(.Rows.Count, 1).End(xlUp).Row, 2) varData = rngRange.Value End With If UBound(varData) 0 Then Application.DisplayAlerts = False On Error Resume Next Worksheets(strTmpSht).Delete On Error GoTo 0: Err.Clear Set wksSht = Worksheets.Add With wksSht .Name = strTmpSht With .Range(strDataStartCell) .Resize(UBound(varData), UBound(varData, 2)).Value = varData .Resize(UBound(varData), 1).RemoveDuplicates Columns:=1, Header:=xlYes End With varName = Intersect(.Range(strDataStartCell, .Cells(.Rows.Count, 1).End(xlUp)), .Range(strDataStartCell, .Cells(.Rows.Count, 1).End(xlUp)).Offset(1)).Value varName = Application.Transpose(varName) With .Range(strDataStartCell) .Resize(UBound(varData), UBound(varData, 2)).Value = varData .Resize(UBound(varData), 1).Offset(, UBound(varData, 2)).Formula = strFormula .Resize(UBound(varData), 1).Offset(, UBound(varData, 2)).Value = .Resize(UBound(varData), 1).Offset(, UBound(varData, 2)).Value .Resize(UBound(varData), 1).Offset(, UBound(varData, 2)).RemoveDuplicates Columns:=1, Header:=xlYes End With varDate = .Range(.Range(strDataStartCell).Offset(1, UBound(varData, 2)), .Cells(.Rows.Count, UBound(varData, 2) + 1).End(xlUp)).Value varDate = Application.Transpose(varDate) .Cells.Clear With .Range(strDataStartCell) .Resize(UBound(varData), UBound(varData, 2)).Value = varData End With End With With wksSht Set rngRange = .Range(strDataStartCell).CurrentRegion rngRange.Resize(1, 1).Offset(, 7).Formula = =IFERROR(TEXT(SUBTotal(105, rngRange.Resize(, 1).Offset(, 1).Address(, , , 1) ),), ) 'Year rngRange.Resize(1, 1).Offset(, 8).Formula = =IFERROR(TEXT(SUBTotal(105, rngRange.Resize(, 1).Offset(, 1).Address(, , , 1) ),), ) 'Month
$$Excel-Macros$$ Re: How to find special characters in a cell
Here's one way to do it in a formula: use this website ( ascii-code.com ) to look up the ASCII equivalent of each character. It turns out to be this: /47 \92 *42 ?63 34 60 62 |124 Then use a FIND formula to look for the ASCII equivalent. I tried the SEARCH formula but it appears to use the ASCII equivalent of * as a wildcard and returns a false positive; the FIND formula does not. If the user's entry is in cell F10, write this formula in another cell: =IF(OR(ISNUMBER(FIND(CHAR(47),F10,1)),ISNUMBER(FIND(CHAR(92),F10,1)),ISNUMBER(FIND(CHAR(42),F10,1)),ISNUMBER(FIND(CHAR(63),F10,1)),ISNUMBER(FIND(CHAR(34),F10,1)),ISNUMBER(FIND(CHAR(60),F10,1)),ISNUMBER(FIND(CHAR(62),F10,1)),ISNUMBER(FIND(CHAR(124),F10,1))),Illegal character in filename, please try again.,Filename OK.) That's a little bulky but gets the job done. -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
$$Excel-Macros$$ Lookup and Replace
Hello, I would like to wish y'all a Happy New year... I am still learning VBA and I really need help: I want to be able to look up for an item and change its price. See attached file.Can someone please help? Thanks. -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. test.xlsm Description: application/vnd.ms-excel.sheet.macroenabled.12
Re: $$Excel-Macros$$ Re: how to remove space between numbers only.
Hi lalit
I don't find any need for that. just working as required by OP
On Wed, Jan 2, 2013 at 3:15 PM, Lalit Mohan Pandey mohan.pande...@gmail.com
wrote:
Nice formula viper but the last space is missing.
On Wednesday, 2 January 2013 15:07:20 UTC+5:30, §»VIPER«§ wrote:
=LEFT(A1,MIN(IFERROR(FIND({0,**1,2,3,4,5,6,7,8,9},A1),))-1)**
SUBSTITUTE(MID(A1,MIN(**IFERROR(FIND({0,1,2,3,4,5,6,7,**8,9},A1),)),255),
,)
this should work with CSE
On Wed, Jan 2, 2013 at 2:55 PM, Pravin Gunjal isk...@gmail.com wrote:
It's absolutely working fine.
Pravin Gunjal
-- Forwarded message --
From: Prince prince...@gmail.com
Date: Wed, Jan 2, 2013 at 11:20 AM
Subject: $$Excel-Macros$$ Re: how to remove space between numbers only.
To: excel-...@googlegroups.com
Hi Manoj,
use this this may help you incase if the Number is any Mobile number:
=MID(A1,1,LEN(A1)-12)**SUBSTITUTE(RIGHT(A1,12), ,)
regards
Prince
On Wednesday, January 2, 2013 11:04:37 AM UTC+5:30, Manoj Kumar wrote:
Hi,
Is there any way out for removing spaces between numbers only. Spaces
between the text should be removed.
For example.
Manoj Kumar 9015 408 078 Manoj Kumar 9015418078
The text at the front is not always fixed. It can contain any number
text and spaces.
Regards,
Manoj
--
Join official Facebook page of this forum @ https://www.facebook.com/**
discussexcel 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 post to this group, send email to excel-...@googlegroups.com.
To unsubscribe from this group, send email to excel-macros...@**
googlegroups.com.
Visit this group at
http://groups.google.com/**group/excel-macros?hl=enhttp://groups.google.com/group/excel-macros?hl=en
.
--
Join official Facebook page of this forum @ https://www.facebook.com/**
discussexcel 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 post to this group, send email to excel-...@googlegroups.com.
To unsubscribe from this group, send email to excel-macros...@**
googlegroups.com.
Visit this group at
http://groups.google.com/**group/excel-macros?hl=enhttp://groups.google.com/group/excel-macros?hl=en
.
--
*Great day,*
*viper*
--
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 post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
--
*Great day,*
*viper*
--
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
Re: $$Excel-Macros$$ Re: how to remove space between numbers only.
Hi Viper,
No issue.. :) i am not pointing you just letting you know. thanks for
the nice formula.
Regards,
Lalit Mohan
On Thursday, 3 January 2013 09:49:40 UTC+5:30, §»VIPER«§ wrote:
Hi lalit
I don't find any need for that. just working as required by OP
On Wed, Jan 2, 2013 at 3:15 PM, Lalit Mohan Pandey
mohan.p...@gmail.comjavascript:
wrote:
Nice formula viper but the last space is missing.
On Wednesday, 2 January 2013 15:07:20 UTC+5:30, §»VIPER«§ wrote:
=LEFT(A1,MIN(IFERROR(FIND({0,**1,2,3,4,5,6,7,8,9},A1),))-1)**
SUBSTITUTE(MID(A1,MIN(**IFERROR(FIND({0,1,2,3,4,5,6,7,**8,9},A1),)),255),
,)
this should work with CSE
On Wed, Jan 2, 2013 at 2:55 PM, Pravin Gunjal isk...@gmail.com wrote:
It's absolutely working fine.
Pravin Gunjal
-- Forwarded message --
From: Prince prince...@gmail.com
Date: Wed, Jan 2, 2013 at 11:20 AM
Subject: $$Excel-Macros$$ Re: how to remove space between numbers only.
To: excel-...@googlegroups.com
Hi Manoj,
use this this may help you incase if the Number is any Mobile number:
=MID(A1,1,LEN(A1)-12)**SUBSTITUTE(RIGHT(A1,12), ,)
regards
Prince
On Wednesday, January 2, 2013 11:04:37 AM UTC+5:30, Manoj Kumar wrote:
Hi,
Is there any way out for removing spaces between numbers only. Spaces
between the text should be removed.
For example.
Manoj Kumar 9015 408 078 Manoj Kumar 9015418078
The text at the front is not always fixed. It can contain any number
text and spaces.
Regards,
Manoj
--
Join official Facebook page of this forum @ https://www.facebook.com/**
discussexcel 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 post to this group, send email to excel-...@googlegroups.com.
To unsubscribe from this group, send email to excel-macros...@**
googlegroups.com.
Visit this group at
http://groups.google.com/**group/excel-macros?hl=enhttp://groups.google.com/group/excel-macros?hl=en
.
--
Join official Facebook page of this forum @ https://www.facebook.com/**
discussexcel 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 post to this group, send email to excel-...@googlegroups.com.
To unsubscribe from this group, send email to excel-macros...@**
googlegroups.com.
Visit this group at
http://groups.google.com/**group/excel-macros?hl=enhttp://groups.google.com/group/excel-macros?hl=en
.
--
*Great day,*
*viper*
--
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 post to this group, send email to excel-...@googlegroups.comjavascript:
.
To unsubscribe from this group, send email to
excel-macros...@googlegroups.com javascript:.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
--
$$Excel-Macros$$ VBA or VSTO
Hi, What is the best option now, VBA or VSTO? Also can you give me its advantages/drawbacks over each other. Thanks, Joseph Sent on my BlackBerry® from Vodafone -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
Re: $$Excel-Macros$$ Lookup and Replace
slightly modified your code. check the attachment On Thu, Jan 3, 2013 at 8:47 AM, Awal malaw...@gmail.com wrote: Hello, I would like to wish y'all a Happy New year... I am still learning VBA and I really need help: I want to be able to look up for an item and change its price. See attached file.Can someone please help? Thanks. -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. test.xlsm Description: Binary data
$$Excel-Macros$$ Re: Macro to create attendance Sheet for each Employee
Hi Rashid, The file which i have sent you contains a sheet named *Sample *so if you have copied only the code then it will give you an error because the sheet is not available. Please check the same. Reagrds, Lalit Mohan On Thursday, 3 January 2013 00:57:11 UTC+5:30, prkhan56 wrote: Hello Lalit, I tried the macro and it works perfectly on the sample data I uploaded in my first post. But very very sorry, I missed to say that Col A has date/Time ad Col B has the Name and not the other way round. Can the code be amended accordingly please. However, if I run your code by interchanging Col A and Col B; I get an error as follows: Run time error '9' - Subscript out of range and it highlights the following line in the code *Worksheets(strSampleFileName).Visible = True* Can you please rectify this problem and thanks a million for all your time and help sorry for my error. Regards Rashid Khan I On Tuesday, January 1, 2013 8:07:35 PM UTC+4, Lalit Mohan Pandey wrote: Hi Rashid, PFA. Regards, Lalit Mohan On Tuesday, 1 January 2013 15:16:42 UTC+5:30, Lalit Mohan Pandey wrote: Hi Rashid, Sorry for that i will send you the file by today evening itself Regards, Lalit Mohan On Tuesday, 1 January 2013 13:37:21 UTC+5:30, prkhan56 wrote: Hello Lalit I cannot see the complete code. If I go and check the original and paste the code I get lot of unwanted characters in the code If you cannot send me in a file then can you please send the code to me as a text file. thanks for your time and help Rashid Khan On Monday, December 31, 2012 3:09:26 PM UTC+4, Lalit Mohan Pandey wrote: Hi Rashid, Sorry i can't upload file so please follow the given steps: *Step 1:- First create a sample sheet for output report with all the formatting and formula and please make sure it should be for 31 rows as * *max day in a month is 31.* *Step 2:- Press Alt + F11 and Alt + I + M and paste below code * * * *Option Explicit Sub GetEmployeeAttendance() Dim wksSht As Worksheet Dim wksReport As Worksheet Dim rngRangeAs Range Dim varData() As Variant Dim varName() As Variant Dim varDate() As Variant Dim varFinal() As Variant Dim lngLoopName As Long Dim lngLoopDate As Long Dim lngCountAs Long Const strFormulaAs String = =A1 | TEXT(B1,m/d/) Const strTmpSht As String = Temp_Sht Const strDataStartCell As String = A1 Const strFinalDataStartCell As String = D8 Const strReportMonthCellAs String = C2 Const strEmpNameCellAs String = C5 Const strSampleFileName As String = Sample ReDim varData(0) With ThisWorkbook.Worksheets(Master) Set rngRange = .Range(strDataStartCell).Resize(.Cells(.Rows.Count, 1).End(xlUp).Row, 2) varData = rngRange.Value End With If UBound(varData) 0 Then Application.DisplayAlerts = False On Error Resume Next Worksheets(strTmpSht).Delete On Error GoTo 0: Err.Clear Set wksSht = Worksheets.Add With wksSht .Name = strTmpSht With .Range(strDataStartCell) .Resize(UBound(varData), UBound(varData, 2)).Value = varData .Resize(UBound(varData), 1).RemoveDuplicates Columns:=1, Header:=xlYes End With varName = Intersect(.Range(strDataStartCell, .Cells(.Rows.Count, 1).End(xlUp)), .Range(strDataStartCell, .Cells(.Rows.Count, 1).End(xlUp)).Offset(1)).Value varName = Application.Transpose(varName) With .Range(strDataStartCell) .Resize(UBound(varData), UBound(varData, 2)).Value = varData .Resize(UBound(varData), 1).Offset(, UBound(varData, 2)).Formula = strFormula .Resize(UBound(varData), 1).Offset(, UBound(varData, 2)).Value = .Resize(UBound(varData), 1).Offset(, UBound(varData, 2)).Value .Resize(UBound(varData), 1).Offset(, UBound(varData, 2)).RemoveDuplicates Columns:=1, Header:=xlYes End With varDate = .Range(.Range(strDataStartCell).Offset(1, UBound(varData, 2)), .Cells(.Rows.Count, UBound(varData, 2) + 1).End(xlUp)).Value varDate = Application.Transpose(varDate) .Cells.Clear With .Range(strDataStartCell) .Resize(UBound(varData), UBound(varData, 2)).Value = varData End With End With With wksSht Set rngRange = .Range(strDataStartCell).CurrentRegion
Re: $$Excel-Macros$$ How to find special characters in a cell
place the name on a1
below formula should work with CSE
=IF(SUM(IFERROR(FIND({/,\,?,,,|,*,},A1),))0,Wrong,Ok)
On Wed, Jan 2, 2013 at 8:34 PM, dannymari...@gmail.com wrote:
In excel 2012 I'm using the name typed in a cell as a part of the filename
when saved via a macro.
Because the following characters /\*?| are not allowed in a filename I
want to do a check in the cell.
The idea is to put a warning in a free cell that the name isn't allowed
when it contains one or more of the forbidden characters.
I used the =IF function in combination with =SEARCH but I don't know how
to use the formulas when I want to check on * ? or
Can somebody help me with that?
Thanks.
Danny
--
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 post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
--
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 post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
