tsaravanasuresh
New Member
i have excel 2003, i want to increase the fields at data form, its maximum is 32 only , i need 100 fields. how can do it. My mail id tsaravanasuresh@gmail.com, pl give solution for it.
excel data
- Thread starter tsaravanasuresh
- Start date
NARAYANK991
Excel Ninja
Hi Suresh ,
Check out this link :
http://www.excelbanter.com/showthread.php?t=143514
Narayan
Check out this link :
http://www.excelbanter.com/showthread.php?t=143514
Narayan
reachkiran03
New Member
Hi Kiran here Query regarding Excel –
1) I store my clients Mobile numbers as 99800 12890 like this I have stored around 500 to 550 numbers.When I want to send a group SMS by using SMS service providers software they insist me to store all the numbers as 9980012890.now I have given space after 99800 12890 do you have any function to remove the space at one go.
2) Now I would like to prefix 91 before mobile number as 919980012890 to all the 500 numbers at one go how to do.
1) I store my clients Mobile numbers as 99800 12890 like this I have stored around 500 to 550 numbers.When I want to send a group SMS by using SMS service providers software they insist me to store all the numbers as 9980012890.now I have given space after 99800 12890 do you have any function to remove the space at one go.
2) Now I would like to prefix 91 before mobile number as 919980012890 to all the 500 numbers at one go how to do.
NARAYANK991
Excel Ninja
Hi Kiran ,
You have already posted this question on this forum , and answers were given ; I do not know why this question should be repeated.
Anyway , I have two things to say :
1. To answer your question , since the existing numbers have spaces within them , they are all text. To remove the space , you can use formulae ; suppose your existing numbers are in column A , from , say , A5 through A500 ; in cell B5 , enter the following formula :
=SUBSTITUTE(A5," ","")
Copy this down , till , say , B500 ; the spaces will be removed.
Now , to add "91" as a prefix to all the modified numbers , you can have the following formula in C5 :
=91&B5
Copying this down till C500 will prefix all the modified numbers with 91.
You can combine both steps in one , and enter the following formula in B5 :
=91&SUBSTITUTE(A5," ","")
2. The other point I want to tell you is that there is no reason for you to enter the 10 digit numbers with a space in between ; you can format the cells where you enter these numbers to show a space in between , so that when you enter the number , you enter all the 10 digits together , without any space between ; when displayed , the numbers will be displayed with a space between the 5th and 6th digits.
Choose a Custom format , and enter 00000 00000 ; even though you enter 9980012890 , it will be displayed as :
99800 12890.
Narayan
You have already posted this question on this forum , and answers were given ; I do not know why this question should be repeated.
Anyway , I have two things to say :
1. To answer your question , since the existing numbers have spaces within them , they are all text. To remove the space , you can use formulae ; suppose your existing numbers are in column A , from , say , A5 through A500 ; in cell B5 , enter the following formula :
=SUBSTITUTE(A5," ","")
Copy this down , till , say , B500 ; the spaces will be removed.
Now , to add "91" as a prefix to all the modified numbers , you can have the following formula in C5 :
=91&B5
Copying this down till C500 will prefix all the modified numbers with 91.
You can combine both steps in one , and enter the following formula in B5 :
=91&SUBSTITUTE(A5," ","")
2. The other point I want to tell you is that there is no reason for you to enter the 10 digit numbers with a space in between ; you can format the cells where you enter these numbers to show a space in between , so that when you enter the number , you enter all the 10 digits together , without any space between ; when displayed , the numbers will be displayed with a space between the 5th and 6th digits.
Choose a Custom format , and enter 00000 00000 ; even though you enter 9980012890 , it will be displayed as :
99800 12890.
Narayan
reachkiran03
New Member
Dear Mr RSK i tried your functions but it is not working.Mr Narayan K 991 Thanks for your help your suggestion is working.
