Hi,
I need a formula or Macro to validate Phone Numbers in a given list "US or UK format". It should check in each cell of a column till the end to make sure it has only numbers and/or characters "(", ")", "+").
I have a sample file but I can't attach due to posting permission.
However, below is an example:
Column A contains list of phone number that need to be validated
Column A Column B
Phone Number Validation
(713) 241 6161 Correct
(203) 207-5000 Error
(213) 620 1780 Correct
(800) 437<2672 Error
(954) 489!9500 Error
(808) 531 3000 Correct
@81 3 5215 9700 Error
+82 2 6360 3000 Correct
Below is the list of acceptable characters. If a cell include only these then in col B should say "Correct"
(, ), +, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9
Below is the list of unacceptable characters if found in a cell then col b should show as "Error":
, , , , , , , ,<column break>, , , , , , , , , , , , , , , , , ¬, , �, �, �, �, �, ', -, *, –, —, , , " , " "
, " , <Page break> , " , " !, ", #, $, %, &, *, ,, ., /, :, ;, ?, @, [, \, ], ^, ˆ, _, `, {, |, }, ~, ¡, ¦, ¨, ¯, ´, ¸, ¿, ˜, ‘, ’, ‚, “, ”, „, ‹, ›, <, =, >, ±, «, », ×, ÷, ¢, £, ¤, ¥, §, ©, ¬, ®, °, µ, ¶, •, †, ‡, •, …, ‰, €, ¼, ½, ¾, ¹, ², ³, A, a, ª, Á, á, À, à, Â, â, Ä, ä, Ã, ã, Å, å, Æ, æ, B, b, C, c, Ç, ç, D, d, Ð, ð, E, e, É, é, È, è, Ê, ê, Ë, ë, F, f, ƒ, G, g, H, h, I, i, Í, í, Ì, ì, Î, î, Ï, ï, J, j, K, k, L, l, M, m, N, n, Ñ, ñ, O, o, º, Ó, ó, Ò, ò, Ô, ô, Ö, ö, Õ, õ, Ø, ø, Œ, œ, P, p, Q, q, R, r, S, s, Š, š, ß, T, t, Þ, þ, ™, U, u, Ú, ú, Ù, ù, Û, û, Ü, ü, V, v, W, w, X, x, Y, y, Ý, ý, Ÿ, ÿ, Z, z, Ž, ž,
I tried using formulas Search and Match but no luck.
It will be great if someone help me with this.
Thank you,
NK
I need a formula or Macro to validate Phone Numbers in a given list "US or UK format". It should check in each cell of a column till the end to make sure it has only numbers and/or characters "(", ")", "+").
I have a sample file but I can't attach due to posting permission.
However, below is an example:
Column A contains list of phone number that need to be validated
Column A Column B
Phone Number Validation
(713) 241 6161 Correct
(203) 207-5000 Error
(213) 620 1780 Correct
(800) 437<2672 Error
(954) 489!9500 Error
(808) 531 3000 Correct
@81 3 5215 9700 Error
+82 2 6360 3000 Correct
Below is the list of acceptable characters. If a cell include only these then in col B should say "Correct"
(, ), +, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9
Below is the list of unacceptable characters if found in a cell then col b should show as "Error":
, , , , , , , ,<column break>, , , , , , , , , , , , , , , , , ¬, , �, �, �, �, �, ', -, *, –, —, , , " , " "
, " , <Page break> , " , " !, ", #, $, %, &, *, ,, ., /, :, ;, ?, @, [, \, ], ^, ˆ, _, `, {, |, }, ~, ¡, ¦, ¨, ¯, ´, ¸, ¿, ˜, ‘, ’, ‚, “, ”, „, ‹, ›, <, =, >, ±, «, », ×, ÷, ¢, £, ¤, ¥, §, ©, ¬, ®, °, µ, ¶, •, †, ‡, •, …, ‰, €, ¼, ½, ¾, ¹, ², ³, A, a, ª, Á, á, À, à, Â, â, Ä, ä, Ã, ã, Å, å, Æ, æ, B, b, C, c, Ç, ç, D, d, Ð, ð, E, e, É, é, È, è, Ê, ê, Ë, ë, F, f, ƒ, G, g, H, h, I, i, Í, í, Ì, ì, Î, î, Ï, ï, J, j, K, k, L, l, M, m, N, n, Ñ, ñ, O, o, º, Ó, ó, Ò, ò, Ô, ô, Ö, ö, Õ, õ, Ø, ø, Œ, œ, P, p, Q, q, R, r, S, s, Š, š, ß, T, t, Þ, þ, ™, U, u, Ú, ú, Ù, ù, Û, û, Ü, ü, V, v, W, w, X, x, Y, y, Ý, ý, Ÿ, ÿ, Z, z, Ž, ž,
I tried using formulas Search and Match but no luck.
It will be great if someone help me with this.
Thank you,
NK
