Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Breaking text on the basis of condition
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 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Breaking text on the basis of condition
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(B 2)-3)) -- HTH, Dianne HI Every one I have a list in two coloumns. First coloumn has country name along with area name. Second is is country code with area code. I want to seperate country code from the area codes, evenif the country name+ area name remains as it is. But i am unable to do so. here is a 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 on post. I will really apprecuate any help on this. Regards JKB Attachment filename: example.xls Download attachment: http://www.excelforum.com/attachment.php?postid=412189 --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Breaking text on the basis of condition
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 --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Breaking text on the basis of condition
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 -- HTH, Dianne 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 --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If condition with text | Excel Discussion (Misc queries) | |||
sum if (text meets condition) | Excel Discussion (Misc queries) | |||
Breaking a string of text | Excel Discussion (Misc queries) | |||
split text in one cell into multiple cells without breaking the wo | Excel Worksheet Functions | |||
breaking text in one cell into two cells | Excel Discussion (Misc queries) |