Find and Replace

[gmoorthy]

In an excel spreadsheet(1) have 2 columns

A and B

Column A has dates and column B has names

like this

Column A Column B

20130102 City
20120811 Country


In Spreadsheet (2) i have 2 columns

Column A Column B
Date=19990102 City
Date=19980810 Country



In the spreadsheet 2 i want the columnA to match up with values from column A in spreadsheet (1) . I cannot use a Vlookup as in the spreadsheet 2 the "Date=YYYYMMDD" is within a string like </XML/paremeter Date = YYYYMMDDD / XML>

 

[Andrzejek]

Would it help if you would add another column?

[pre]
A B C
1 Date=19990102 City 19990102
2 Date=19980810 Country 19980810
[/pre]

And in cell C1 is a formula: [tt]=MID(A1, 6, 8)[/tt]

Have fun.

---- Andy

 

[gmoorthy]

That will not work since the column A in spreadsheet 2 is as below

<batchJobCommand xmlns="http:<jobName>ABC</jobName><operation>XYZ</operation><jobParameters>date=YYYYMMDD</jobParameters></batchJobCommand>

 

[byundt]

You might consider a formula that uses MID and SEARCH to extract the 8 digit date:
=VLOOKUP(--MID(A1,SEARCH("Date=",A1)+5,8),Sheet1!A:B,2,FALSE)

Multiplying by two minus signs in a row converts the text returned by MID into a number. I assumed that column A in Sheet1 contains numbers, not text.