Convert strings

[hanasamo]

A B
PEK BEIJING
SHA SHANGHAI
CAN GUANGZHOU

In sheet1 I have a database like the above, the column A is city code, and the column B contains theie corresponding city name.

In sheet2 there are citypairs as follows:
PEK-SHA
PEK-SHA-PEK
PEK-SHA-CAN-SHA
PEK-SHA-CAN-SHA-PEK

What I want to do is convert the citypairs into their actual city names, for example, PEK-SHA-CAN-SHA, I want it to be BEIJING-SHANGHAI-GUANGZHOU-SHANGHAI. I have tried a several time by using functions, mid, left,right,index,match,if, but I failed because it's too long to be kept correct. Can you experts tell me how to do it, VBA and functions are both fine. Thanks in advance.

 

[SteveO59L]

Are your sheet2 values in seperate columns ?

 

[hanasamo]

Are your sheet2 values in seperate columns ?

No, they are in one column

 

[al_b_cnu]

Hi,

A VBA UDF solution, assuming you have Excel 2002 or higher:

Function CityCodes(ByVal Codes As String, ByVal CodeTable As Range)
Dim iPtr As Integer
Dim rTable As Range, rBase As Range
Dim vElements As Variant, vResult As Variant
Dim wsTable As Worksheet

Set wsTable = Sheets(CodeTable.Parent.Name)
Set rBase = wsTable.Range(Cells(CodeTable.Row, CodeTable.Column).Address)

Set rTable = wsTable.Range(rBase.Address, _
                           wsTable.Cells(CodeTable.Row + CodeTable.Rows.Count - 1, _
                                         CodeTable.Column).Address)
vElements = Split(Codes, "-")
For iPtr = 0 To UBound(vElements)
    vResult = "*"
    On Error Resume Next
    vResult = WorksheetFunction.Match(vElements(iPtr), rTable, 0)
    On Error GoTo 0
    If IsNumeric(vResult) = True Then
        vElements(iPtr) = rBase.Offset(vResult - 1, 1).Text
    End If
Next iPtr
CityCodes = Join(vElements, "-")
End Function

Book1
	A	B	C	D
2	PEK-SHA	BEIJING-SHANGHAI
3	PEK-SHA-PEK	BEIJING-SHANGHAI-BEIJING
4	PEK-SHA-CAN-SHA	BEIJING-SHANGHAI-GUANGZHOU-SHANGHAI
5	PEK-SHA-CAN-SHA-PEK	BEIJING-SHANGHAI-GUANGZHOU-SHANGHAI-BEIJING
Sheet2

Book1
	A	B	C	D
1	Code	City
2	PEK	BEIJING
3	SHA	SHANGHAI
4	CAN	GUANGZHOU
Sheet1

Formula in B2 & copied down is =citycodes(A2,Sheet1!A:B)

 

[hanasamo]

Thanks so much al_b_cnu. Your functions works fine, Thanks again

 

[jindon]

Hi
using Replace Method

Sub test()
Dim a, i As Long
a = Sheets("sheet1").Range("a1").CurrentRegion.Resize(, 2).Value
For i = 1 To UBound(a, 1)
    With Sheets("sheet2")
        .Columns("a").Replace what:=a(i, 1), replacement:=a(i, 2)
    End With
Next
Erase a
End Sub