Best way to......

[darrent]

....insert "00" in front of a big list of phone numbers. (The symbol for plus is not working on my keyboard btw hence "plus" )

I need to insert 00 in front of a huge list of phone numbers. At the minute they have the "plus" sign instead of 00 - although you can obviously call internationally using either "plus" or 00 the billing system being used does not understand "plus" only 00. The phone numbers are in notepad - what program would be best to insert 00 in front of them all???

>>> Edited by darrent on Wednesday 17th September 10:06

[ben789]

crtl-h (replace)

Find What: +
Replace with: 00

Replace all

[pdV6]

What's wrong with search & replace in Notepad?

Ctrl-H
Find What: '+'
Replace with: '00'

Too slow!

>> Edited by pdV6 on Wednesday 17th September 10:16

[darrent]

Sorry guys I think I misled you all - the "plus" sign is not actually on the list in Notepad it's just listed as 44 hence when you try find and replace all, there is actually nothing to replace!!

[pdV6]

Replace "44"
With "0044"

Er, unless there are any numbers containing "44" D'OH!



>> Edited by pdV6 on Wednesday 17th September 15:55

[darrent]

Yep - lots of numbers with 44!!

[liszt]

bung it in to excel and create column a with '00 (to make a text "00". have numbers in coumn b in column c have =CONCATENATE(A1,B1)
copy column C then paste special to another column.
delete all other columns.
now save in format of your choice

[ehasler]

Copy the list into one column (B)in Excel, and create another column (A) with just 00 in it.

Then create a third column with a formula to join them together - =CONCATENATE(A1,B1)

You'll need to set the format of A to be Text so that "00" doesn't get turned into "0", but this should do the job.

[ehasler]

Great minds think alike!

[Mr E]

Sed.....


man sed on any unix system will tell you how.

Awk will do it too, but it's a sledgehammer to crack a nut.

[darrent]

Ah! Thanks guys will give it a go!!!!!

[darrent]

Ah! Now then - this works fine....but how would I also do this in Access??!!

[Plotloss]

Cant you export the XL sheet as an Access DB?

Or is it a different list of numbers?

[darrent]

The problem is that the list is over 65k rows (or what ever the max is in Excel) so it won't do it all and it's far to big to chop into sections and do each one at a time hence the need for a Access solution

[Plotloss]

vbScript for you then my man.

I thought that XL now doesnt have a maximum...?

[darrent]

Hmmm...don't know sh!t about VB so stuck again! Excel - mine certainly does but it's only Excel 2000

[liszt]

Get the data into a table .

Create a query. Select the SQL view.

Copy this in
UPDATE [My table]
SET [My table].[telephone number] = "00" & Mid([My table]![telephone number],2,20);

need to set the table name and the field name as appropriate. run query

[darrent]

How do I select SQL view from query?

[liszt]

In the query just select a design view. Doesn't matter what is in it.

Then in the tool bar the left most icon is a grid with a small arrow next to it. This will allow you to switch between design view, SQL view and results pane.

Just clic on the yiny down arrow and select SQL

or click view -> SQL view in the menu bar

[darrent]

Sorry mate - only got design view and wizard - I must not have SQL view (or it's not switched on)??