Splitting International Dialing codes into country and area code
Good day,
I have the following columns :
A
Dailing Codes
1 93
2 937
3 355
4 35538
5 35566
6 213
7 2135
8 2136
9 1684
.
.
.
.
.
.
------
B
Country Codes
1 93
2 355
3 213
4 376
5 244
6 1
7 44
.
.
.
.
.
---------
Please assist with the following :
Match the Dialing codes against the country codes from Left to right. if match is found return the country code in column C and the area code in column C. E.G. Dialing code 93(A1) is matched against Array (B1:B210) Country Codes. As 93 is found in the Country Codes array , 93 will be returned as Country Code. Next row : Dialing code 937(A2) is matched against Array (B1:B210) Country Codes. As 93 is found in the Country Codes array , 93 will be returned as Country Code and 7 will be returned as area code. Please refer to attached sample File for sample data.
Kind regards
Stefan
| Attachment | Size |
|---|---|
| Test Sheet.xlsx | 87.79 KB |
Hi Stefan, this code does the
Hi Stefan,
this code does the job
Sub Leo()
Dim test As Integer, rFoundCell As Range, lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
Set rFoundCell = Range("C1")
On Error Resume Next
For Each cl In Range("A2", "A" & lr)
test = Left(cl, 1)
Set rFoundCell = Columns(3).Find(What:=test, After:=rFoundCell, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not rFoundCell Is Nothing Then GoTo vervolg
If rFoundCell Is Nothing Then
Set rFoundCell = Range("C1")
test = Left(cl, 2)
Set rFoundCell = Columns(3).Find(What:=test, After:=rFoundCell, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not rFoundCell Is Nothing Then GoTo vervolg
If rFoundCell Is Nothing Then
Set rFoundCell = Range("C1")
test = Left(cl, 3)
Set rFoundCell = Columns(3).Find(What:=test, After:=rFoundCell, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not rFoundCell Is Nothing Then GoTo vervolg
End If
End If
vervolg:
cl.Offset(, 4) = test
cl.Offset(, 6) = Right(cl, (Len(cl) - Len(cl.Offset(, 4))))
Next
End Sub
Kind regards
Leo