• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

phone number updation tricks

V

vvgiri

New Member
Dear friends


i have a database of members with lots of details which is being maintained over 5 years now. couple of years ago, the phone numbers have changed from 7 digit to 8 digits. We have different series of phone numbers starting from 3, 4 5, 6 series. I need to add additional digit before each number. 3 for 3 series, 4 before 4 series and so on. we have over 500 members details in our databese, it will consume lot of time to manually update these. How can i do that this using excel formulas or macros? i am still learning excel , i am struggling to do that. second thing, some of the phone numbers of the newly joined members already entered in eight digit s. How can i solve this problem, make uniform the phone numbers columns.


Thanks and regards,


Giri
 
hey vvgirl

check the following link and download the file


http://www.2shared.com/file/XMoaBHuw/phone_book.html


then tell me is it ok or not?

if not then can u provide a sample file.

Regards

CA Mahaveer Somani
 
Hey vvgiri


I have created one sample excel sheet with macro in it.


I hope this Macro will fulfill your current requirement of updating the numbers.


I can mail this sample excel sheet to you.


Give me test mail on wade2586@gmail.com
 
Check this link to get the file I have been talking about


http://www.filedropper.com/dictionaryupdate
 
Hi v4wade,


I am sorry but I am not able to see any file in the link you have posted; indeed, I am getting error.So I am not able to see any sample data layout your are talking about.


But from the explanation of your post I understand that:


1)You want to get the length of the number (whether 3 series / 4 series / 5 series etc..)


2)You want to add 3 before 3 series number, 4 before 4 series number so on and so forth.


If my understanding is correct, then lt's assume below is your data layout(in Col A from A1 to A6):

[pre]
Code: 
123
1234
12345
123456
1234567
12345678
[/pre]

At B1 write the below formula, press enter and drag it down:


=LEN(A1)&A1


Does it solve your problem?


Kaushik.
 
Hi vvgiri,


You can try a formula on below lines. Assuming that your original data is in A2, put this formula in another cell:

=IF(LEN(A2)=8,A2,IF(LEN(A2)=7,LEFT(A2)&A2,"CHECK"))
 
Dear Mahavir , Wade and Kuashik

thank you all for the support. My Problem has been solved now.


Dear Wade, i am bit new to macros, though my problem is solved, i cound not figure out how this macro is created.

Thank you all


Regards,


Giri
 
Thank you Shrivallabha, i tried the formula you provided, it worked. Now learnt several ways to achieve this.

Thank you all, and it is really use full for learners like me.


Giri
 
Good day Kaushik03


The link on filedropper is very slow and when you do get in you have to enter an annoying code, I have uploaded v4wade's file to dropbox.


https://dl.dropbox.com/u/75495784/Dictionary%20Update.xlsm
 
Hi v4wade,


I am so much impressed with your

[list type=decimal]
[*]Indentation.
[*]Internal Documentation.
Environment Declaration.
& Finally Message in msgbox..
[/list type=decimal]

Welcome to the forum.. and Wish you a pleasure journey with us..


Regards,

Deb

PS: for the community..here is the code.. Simple but full of documentation..

__________

[pre]
Code: 
Sub Button1_Click()
' Developed in MS Excel 2007 under Windows XP SP3
Dim m
m = MsgBox("PLEASE MAKE A BACKUP COPY OF YOUR ACTIVE WORKSHEETs BEFORE CONTINUING THIS MACRO" _
& vbNewLine & vbNewLine & "CONTINUE EXECUTING THE MACRO" _
, vbOKCancel Or vbCritical, "CAUTION")
If (m = vbOK) Then
Dim cnt As Integer ' Ref. variable for code
Dim dig1 As Integer ' 1st digit of contact no in Column A
Dim origVal ' Original number in Column A
Dim tmp, min, max
min = 2 ' start from Cell A2
max = 5000 ' end at cell A5000
' Adjust max = 5000 to anything less than 1048576
' max row limit of MS Excel 2007 is 1048576
For cnt = min To max
tmp = Sheet1.Range("A" & cnt).Value

' Skip if row is empty
If IsEmpty(tmp) Then
' do nothing
Else ' IsEmpty(tmp)
If IsNumeric(Sheet1.Range("A" & cnt).Value) Then
' Copy original value
origVal = Sheet1.Range("A" & cnt).Value

' Extract 1st digit of the number
dig1 = Left((Sheet1.Range("A" & cnt).Value), 1)

' Paste final variable in Column C
Sheet1.Range("C" & cnt).Value = dig1 & origVal
Else ' IsNumeric(Sheet1.Range("A" & cnt).Value)
' Do nothing
End If ' IsNumeric(Sheet1.Range("A" & cnt).Value)
End If  ' IsEmpty(tmp)
Next ' cnt = min To max
MsgBox "Done", vbOKOnly Or vbInformation, "Success"
Else ' (m = vbOK)
MsgBox "Ok buddy, I will not execute the Macro" _
& vbNewLine & "Please SAVE YOUR ACTIVE WORKSHEETs from next time" _
, vbOKOnly Or vbInformation, "Stop"
End If ' (m = vbOK)
End Sub
[/pre]
 
Dear friends,


i have one more query. I have updated database with new phone numbers. How to delete old contacts column?

since it is used in the formula, is there any way to do this other than hiding the column? I want to do the same for Family Name/Name Columns coz i have created full name Column instead of Name/Family name columns using "& "formula i learnt today


Giri
 
vvgiri


Just run your code/formula/function and when you have the column of data you want select all of it copy and then paste as VALUE over the original column of data. Once you have do this the original data and any attached code/formula/function will be gone
 
You must log in or register to reply here.
Back
Top