replacing and adding text HELP

Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: replacing and adding text HELP

  1. #1
    New Member
    Join Date
    May 2003
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default replacing and adding text HELP

     
    Hi!
    I have 3 rows of info which i need to create formula's for to do the following:
    One column is of mobile numbers some of them are missing a "0" from the front of them. I need to have the old mobile number replaced with the new one with a "0" in front of it.
    One column is of email addresses. I need to add a ".au" to 1 group of email addresses with a specific domain.
    Another column i have a value and i need it to have 200 removed from the value already existing in the cell if it is over 800 and be replaced with the result of the value-200

  2. #2
    Board Regular
    Join Date
    Jul 2002
    Posts
    603
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: replacing and adding text HELP

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book1___Running: xl2002 XP : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    E
    1
    2
    1234560123456
    3
    2523620252362
    4
    15253601525361000800
    5
    800800
    6
    12001000
    7
    Sheet1

    [HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    What is that paritucular group of e-mail Id?
    Regards
    GNaga

  3. #3
    New Member
    Join Date
    May 2003
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: replacing and adding text HELP

    That is ok, but i need the result of the formula to be in the original text.
    Therefore the info in B should overwrite the original data.
    it needs to replace the original text.
    Any idea?

  4. #4
    Board Regular
    Join Date
    Jul 2002
    Posts
    603
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: replacing and adding text HELP

    Can you give me the key to identify the group of e-mail? so that altogether I can try for VBA solution.
    Regards
    GNaga

  5. #5
    New Member
    Join Date
    May 2003
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: replacing and adding text HELP

    I need to add ".au" to all Domains which have onnet.com
    it also needs to replace the original email with the new one.
    thanks so much for your time and assistance

  6. #6
    Board Regular
    Join Date
    Jul 2002
    Posts
    603
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: replacing and adding text HELP

    All the e-mail id ending with onnet.com should now be end with .au Am I correct?
    Regards
    GNaga

  7. #7
    New Member
    Join Date
    May 2003
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: replacing and adding text HELP

    I can email u the file if its easier?
    the email addresses have a username@onnet.com
    needs to be replaced with username@onnet.com.au

  8. #8
    New Member
    Join Date
    May 2003
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: replacing and adding text HELP

    yes thats correct

  9. #9
    Board Regular
    Join Date
    Jul 2002
    Posts
    603
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: replacing and adding text HELP

    Sub Test()
    Dim I As Long

    For I = 1 To Worksheets(1).Range("A1").End(xlDown).Row
    Worksheets(1).Range("A" & I).NumberFormat = "@"
    Worksheets(1).Range("A" & I) = "0" & Worksheets(1).Range("A" & I)

    If Worksheets(1).Range("B" & I) > 800 Then _
    Worksheets(1).Range("B" & I) = Worksheets(1).Range("B" & I) - 200

    If Trim(Mid(Worksheets(1).Range("C" & I), InStr(1, Worksheets(1).Range("C" & I), "@") + 1, _
    Len(Worksheets(1).Range("C" & I)) - InStr(1, Worksheets(1).Range("C" & I), "@"))) = "onnet.com" Then
    Worksheets(1).Range("C" & I) = Left(Worksheets(1).Range("C" & I), InStr(1, Worksheets(1).Range("C" & I), "@")) & ".au"
    End If
    Next I
    End Sub


    Try this code by keeping backup copy of your original file.
    Regards
    GNaga

  10. #10
    Board Regular
    Join Date
    Jul 2002
    Posts
    603
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: replacing and adding text HELP

      
    Try this one. Not the earlier one

    Sub Test()
    Dim I As Long

    For I = 1 To Worksheets(1).Range("A1").End(xlDown).Row
    Worksheets(1).Range("A" & I).NumberFormat = "@"
    Worksheets(1).Range("A" & I) = "0" & Worksheets(1).Range("A" & I)

    If Worksheets(1).Range("B" & I) > 800 Then _
    Worksheets(1).Range("B" & I) = Worksheets(1).Range("B" & I) - 200

    If Trim(Mid(Worksheets(1).Range("C" & I), InStr(1, Worksheets(1).Range("C" & I), "@") + 1, _
    Len(Worksheets(1).Range("C" & I)) - InStr(1, Worksheets(1).Range("C" & I), "@"))) = "onnet.com" Then
    Worksheets(1).Range("C" & I) = Worksheets(1).Range("C" & I) & ".au"
    End If
    Next I
    End Sub
    Regards
    GNaga

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