Formatting Phone and Celular Numbers

Hi Bucko,

Your sollution works brilliantly! I was just wandering what RowCnt = 2 did exactly and what 12 in Cells(RowCnt, 12) stood for. Can you elaborate a bit on these two questions?

In any case, thanks a lot for your assistance!

Regards,
Andrei

Why not just format the cells, why do it in code?

And that... is a good question, Kyle! How can I format the cells for both mobile and phone numbers just by selecting the column once?

Regards,
Andrei

Use conditional formatting, test if the cell > 100000000

Well, I think there might be a problem with CF in my case. Besides digits the cells contain other characters such as commas, brackets, dashes and "abc"-characters. The whole idea is to extract digits from a string of characters and format these digits as phone and celular numbers. (E.g. L2 = "03 7774684, [email protected]" --> 03 777 46 84)
Please, correct me if I think incorrectly but your sollution implies that the cells are formatted as values not as text and the first digit must be higher of equal to 1.

Regards,
Andrei

Yes, you're right

The only thing to consider with your cleaning function is that id there are any numbers in the email address, they will be added to the phone number

Jesus Christ and his mother Mary, I haven't even considered this possibility. Any idea's on how I can select the first 9 digits if second digit is 3 or 9 and 10 digits if second digit is 4 ? (03 XXX XX XX and 04XX XX XX XX)

Regards,
Andrei

Can you give some examples of the data you have to deal with? Just trying to see if there are consistent themes or whether the data is all inconsistent and a mess

Actually, try this function

Please Login or Register  to view this content.

This is an example of the data, Kyle. The data is quite inconsistent as you can see.

I was wandering whether it would be possible to delete everything after the first comma and use CF for the formatting?
I'm trying to avoid functions and public functions as I'll have to explain to unexperienced excel users how to use my macros when I'm out of the office.

I sincerely appreciate your effort, Kyle

regards,
Andrei

This worked for me for all your examples (using conditional formatting)

Please Login or Register  to view this content.

@ Kyle Your sollution works great! You have been really helpful in my thread. Thanks a lot for that. Unfortunatelly, I can't give you more reputation points in this thread otherwise I would give you 1000 EXP Thanks again.
@Jindon Your function is a piece of programming art. Thanks for your effort as well.