Find and replace 61 at the start of numbers but not within

Hi Guys,

I have a phone bill I'm trying to audit, for some reason mobile numbers are sometimes presented as #### ### ###, other times as +61### ### ###, other times 61### ### ###. I suspect this represents the actual dialed number, but thats not really important.

I want to clean up the prefixes so I can use a VLOOKUP function to identify the numbers, but to do this, the numbers need to be in a standard format, which I would like to be the #### ### ### format. (it will actually be ### ### ### because the program I use to export the data to excel removes the leading 0, which no amount of formating in excel will return. I suppose I could manually add, or even write a macro to add it, but I can also just adjust my VLOOKUP database as a once off and never worry about it ever again).

getting rid of the +61 is easy. Find all replace all "+61" with "blank". When I do this for "61" it finds 61 at the start of the number, but also within the number. I initially got around this by telling it to find all "61*********" but when I replace all with "blank" it deletes the number entirely, I suspect because of the asterix's....asterii?.....whatever. But without the asterixesesesii it will find and replace 61 within the number as well!

Help please?

Thanks guys!

You could do it with a formula. Assume your numbers are in column A, then insert a new column B and put this formula in B2:

=IF(LEFT(SUBSTITUTE(A2,"+",""),2)="61",RIGHT(SUBSTITUTE(A2,"+",""),LEN(SUBSTITUTE(A2,"+",""))-2,A2)

then copy this down. Then fix the values, and then you can delete column A to leave you with the same layout as you started with.

Hope this helps.

Pete

Originally Posted by martindwilson

and those that loose the leading 0 are probably formatted as numbers. so a few of each will do

I initially thought the same, but they are already formatted as numbers, the program that exports the data literally does not record a leading 0

Originally Posted by Pete_UK

=IF(LEFT(SUBSTITUTE(A2,"+",""),2)="61",RIGHT(SUBSTITUTE(A2,"+",""),LEN(SUBSTITUTE(A2,"+",""))-2,A2)

The problem with this approach is that I have 305 individual worksheets, each of which represents an individual phone bill. I already have the macro that for each worksheet, it selects the right column, finds things such as MNET, MNE, +61 and deletes them (well, replaces them with 'blank'), but that works because those values are only at the start, or end, of the number, never in the middle. when I run the macro for find replace 61 it works at the start, but also removes 61 if it appears within the middle of the number as well

Originally Posted by msexcelathome

Please attach a sample file.

will spoof one up now. Can't use the real data as the phone numbers are sensitive

Numbers example.xlsm

here 'tis.

Thanks for the attempts guys, but for the volume of data I'm dealing with these solutions didn't really work. I'd give you a copy of the data, but spoofing it would take days and its all sensitive company info.

i've decided to just 'double up' my identities in the vlookup database, and present both the 61######### number and the ######### number. This is likely considered a 'kludge' in programming circles, but as illerate as I am in programming, my boss is even less so, so my kludge is likely to go unnoticed and unlikely to hurt anyone/thing other than the CPU that has to run through a double length VLOOKUP table :P

Pl see the attached file.
Instead of using VOOKUP formula you can use the ARRAY formula given in file.There is no need of altering your original Database.

Try removing the + and use this =IF(AND(LEFT(A8,2)="61",LEN(A8)=11),"0"&RIGHT(A8,9),"")

I downloaded your xlsm (Numbers example ans.xlsm‎) but do not understand the formula. Can you break it down further (for the dummies) i.e. column a has numbers step one ??? step two ????
Do you need to anything to settings & does the formula work in office 2007 and 2010?