Breaking text on the basis of condition

[jkb1249]

HI Every one

I have a list in two coloumns. First coloumn has country name alon
with area name. Second is is country code with area code.
I want to seperate country code from the area codes, evenif the countr
name+ area name remains as it is. But i am unable to do so. here is
example

Country NAME Country CODE
ALGERIA 213
ALGERIA Mobile 21361
ALGERIA Mobile 2137
AMERICAN SAMOA 684
AMERICAN SAMOA Mobile 6842
ANDORRA 376
ANDORRA Mobile 3763
ANGOLA 244
ANGOLA Mobile 244912
ANGOLA Mobile 244915
ANGOLA Mobile 244923


Now I want to make them in three coloumns

Country NAME Country CODE Area CODE
ALGERIA 213
ALGERIA Mobile 213
61
ALGERIA Mobile 213
7
AMERICAN SAMOA 684
AMERICAN SAMOA Mobile 684 2
ANDORRA 376
ANDORRA Mobile 376
3
ANGOLA 244
ANGOLA Mobile 244 912
ANGOLA Mobile 244 915
ANGOLA Mobile 244 923

see emaple attached as the above will not come properly formated o
post. I will really apprecuate any help on this.

Regards

JK

Attachment filename: example.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=41218

 

[Dianne]

If the country code is ALWAYS three characters long, then use the
following 2 formulas to get the country code:

=IF(ISERROR(SEARCH("Mobile",A2)),B2,LEFT(B2,3))

and area code:
=IF(ISERROR(SEARCH("Mobile",A2)),"",RIGHT(B2,LEN(B2)-3))

 

[jkb1249]

hi

thanks for the reply. but the country code is not always 3 digits. more
over it can be anything from 2 to 8 digits and country code part may be
anything from 2 to 4/5 within that with no seperation marks.

any idea ?

rg
jkb

 

[Dianne]

Try this:

Sub ParseCodes()

Dim shtData As Worksheet
Dim shtNew As Worksheet
Dim rngData As Range
Dim c As Range
Dim strCountry As String
Dim strCountryCode As String
Dim strAreaCode As String
Dim strValue As String
Dim lngRow As Long

'Change this to reflect the location of your data
Set shtData = ActiveWorkbook.Worksheets("Sheet1")

'Delete existing "Parsed" worksheet if it exists
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets("Parsed").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a worksheet "Parsed"
Set shtNew = ActiveWorkbook.Worksheets.Add
shtNew.Name = "Parsed"

'assumes your data starts in column A
With shtData
Set rngData = .Range("A2", .Range("A" & .Range("A" &
..Rows.Count).End(xlUp).Row))
End With

With shtNew
.Range("A1").Value = "Country"
.Range("B1").Value = "Country Code"
.Range("C1").Value = "Area Code"
.Rows(1).Font.Bold = True
End With

lngRow = 2

'assumes your actual data starts in row 2
For Each c In rngData
strCountry = Trim(c.Value)
If Right(strCountry, 6) <> "Mobile" Then
strCountryCode = Trim(c.Offset(0, 1).Value)
With shtNew
.Cells(lngRow, 1).Value = strCountry
.Cells(lngRow, 2).Value = strCountryCode
End With
Else
strAreaCode = Trim(c.Offset(0, 1).Value)
strAreaCode = Right(strAreaCode, Len(strAreaCode) -
Len(strCountryCode))
With shtNew
.Cells(lngRow, 1).Value = strCountry
.Cells(lngRow, 2).Value = strCountryCode
.Cells(lngRow, 3).Value = strAreaCode
End With
End If
lngRow = lngRow + 1
Next c

shtNew.Columns("A:C").AutoFit

Set c = Nothing
Set rngData = Nothing
Set shtNew = Nothing
Set shtData = Nothing

End Sub