Data transformation in Excel cells.

[bartszu]

Hi All.

I have following 1 problem or rather 2 problems:

Problem 1:

I have column in Excel which is a phone number column.
So all numbers in this column are phone numbers.

Problem is that this column is edited by few people.
So in there I have entries like:
For example:

123-123-1234

Many of entries like this.

Some entries look like:

(123)-123-1234

Well problem is that in order for dialler to dial properly from cell number has to be plain.
I mean has to have no - or () signs in them.

So I would like to convert column of data looking like.

123-123-1234

to

1231231234

Question ? Is there any way to convert entire column this way in Excel ???

Problem 2:

On some sheets I have only numbers from particular country e.x. Colombia.
So numbers are ok, in therms of formatting so they go like 0123456789.
But in order for dialler to dial them correctly they need all to have Colombian prefix in front of them, which is 0057.

So in this case I would like to convert entire column from this:

0123456789

to this

00570123456789

And here comes second question ?
Is there a way in Excel to convert entire column this way ??

I will a lot appreciate any answer and help.
Thanks

[daffodil11]

Question 1:

Please Login or Register  to view this content.

And copy down as needed

Question 2:

Please Login or Register  to view this content.

And copy down as needed

Please see attached

numbers.xlsx


As for changing all of the cells without formulas, you would want to post a question in the VB forums for lines of code to accomplish this.

[L-Drr]

For question 1, if you really want to be absolutely sure there are no other characters but numbers in it, watch this video (found in another thread):

http://www.youtube.com/watch?v=bqsvOygpQWc

[bartszu]

Question 1:

Please Login or Register  to view this content.

And copy down as needed

This is working perfect so far
Thanks testing second solution
THX

[bartszu]

This is working perfect so far
Thanks testing second solution
THX

Well second formula is working perfect too.