Help with converting

[melaniews]

Hi,
I know this is probably really simple but I'm just not getting it.

I run a query and make a table with the desired fields. Then I export the table to excel. The problem is that some of the data in the phone field is formatted 000-000-0000 and some is not. So it shows up in the new table and in Excel in a mixed format.

I would like to do a mass change on this field and don't know how. I need very specific instructions.

TIA,
Melanie

 

[WildHare]

I would like to do a mass change on this field and don't know how

From what, to what? Not exactly sure what mass change you mean, but here's the general idea.

From your description, the PhoneNum is a text field, and some records have the dashes in there, and some dont.

So, if you want to to TAKE THE DASHES OUT, here's what to do.
1) Update query.
2) Drag PHONENUMBEr to grid.
3) Change UPDATE TO cell to

= Left(PhoneNum,3) & Mid(PhoneNum,5,3) & Mid(PhoneNum,9,4)

4) In the CRITERIA cell, put this : MID(Phone,4,1) = "-"

Run the query.

It should strip out the dashes in Position 4 and 8, but only for those records where there is a DASH in position 4.

See how easy that was?

If you want to ADD the dashes, just use the same basic logic, but the other way around.

Left(Phone,3) + "-" & Mid(Phone,4,3) & "-" & Right(Phone,4)

will take 8562340980

and make it 856-234-0980






Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[melaniews]

Thanks that's exactly what I was looking for.

Melanie