replacing and adding text HELP

treacle

New Member
Joined
May 26, 2003
Messages
13
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 :rolleyes: 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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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?
 
Upvote 0
Can you give me the key to identify the group of e-mail? so that altogether I can try for VBA solution.
 
Upvote 0
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
 
Upvote 0
All the e-mail id ending with onnet.com should now be end with .au Am I correct?
 
Upvote 0
<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Test()
    <SPAN style="color:#00007F">Dim</SPAN> I <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    
    <SPAN style="color:#00007F">For</SPAN> I = 1 <SPAN style="color:#00007F">To</SPAN> Worksheets(1).Range("A1").End(xlDown).Row
        Worksheets(1).Range("A" & I).NumberFormat = "@"
        Worksheets(1).Range("A" & I) = "0" & Worksheets(1).Range("A" & I)
        
        <SPAN style="color:#00007F">If</SPAN> Worksheets(1).Range("B" & I) > 800 Then _
            Worksheets(1).Range("B" & I) = Worksheets(1).Range("B" & I) - 200
        
        <SPAN style="color:#00007F">If</SPAN> 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" <SPAN style="color:#00007F">Then</SPAN>
            Worksheets(1).Range("C" & I) = Left(Worksheets(1).Range("C" & I), InStr(1, Worksheets(1).Range("C" & I), "@")) & ".au"
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> I
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Try this code by keeping backup copy of your original file.
 
Upvote 0
Try this one. Not the earlier one

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Test()
    <SPAN style="color:#00007F">Dim</SPAN> I <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    
    <SPAN style="color:#00007F">For</SPAN> I = 1 <SPAN style="color:#00007F">To</SPAN> Worksheets(1).Range("A1").End(xlDown).Row
        Worksheets(1).Range("A" & I).NumberFormat = "@"
        Worksheets(1).Range("A" & I) = "0" & Worksheets(1).Range("A" & I)
        
        <SPAN style="color:#00007F">If</SPAN> Worksheets(1).Range("B" & I) > 800 Then _
            Worksheets(1).Range("B" & I) = Worksheets(1).Range("B" & I) - 200
        
        <SPAN style="color:#00007F">If</SPAN> 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" <SPAN style="color:#00007F">Then</SPAN>
            Worksheets(1).Range("C" & I) = Worksheets(1).Range("C" & I) & ".au"
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> I
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top