Access Poser
Access Poser
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Access Poser

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Liverpool UK
    Posts
    66
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I realise this is not the place.... But my recordset is nearly 1 million. So Access would be the easiest way forward.

    I have tel numbers containing all sorts of spaces, dashes etc,in one column. I want to run an update query to an empty column which removes blanks and dashes leaving the tel no as a the numbers

    ie 01253-202121 would be 01253202121

    This is in order that I can lookup and match on telno from another list. Any help would be appreciated

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm not familiar with the VBA for Access, but in excel you could do it in the following way:
    Code:
    Sub OnlyNumbers()
    Dim FinalStr As String
    For i = 1 To Len(ActiveCell.Value)
    If IsNumeric(Mid(ActiveCell.Value, i, 1)) = True Then FinalStr = FinalStr + Mid(ActiveCell, i, 1)
    Next
    ActiveCell.Value = FinalStr
    _________________
    Hope this helps.
    Kind regards, Al.

    [ This Message was edited by: Al Chara on 2002-04-18 10:10 ]

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Liverpool UK
    Posts
    66
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    thanks Al, thats great, its just the volume of numbers....

  4. #4
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    If you could get your data into excel then you could throw the code into a FOR loop that will do EACH cell in the ACTIVESHEET's USEDRANGE.
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com