Excel parsing and converting text

Question

I need to be able to convert cells from one format to another according to the following rules:

Property Description    --enter as--    Folio Identifier
----------------------------------------------------------
Lot 23 DP789678                         23/789678
Lot 7 Section 12 DP6789                 7/12/6789
Lot 1 SP 45676                          1/SP45676
Common Property Title SP45676           CP/SP45676
Volume 7456 Folio 56                    7456-56
Auto-Consol 5674-78                     5674-78
Water Access Licence No. 123            WAL123

So the thing on the left has to be changed to the item on the right. For example, if I have any cells that look like Lot 23 DP789678, they must be converted to Lot 23 DP789678. How do I write a formula that will do this for all the above cases?

Answer 1

You can use the MATCH and INDEX functions. Suppose Property Description and Folio Identifier are in columns A1:A7 and B1:B7 respectively, and the string you want to replace is in A12, then let B12 be:

=INDEX(B1:B7,MATCH(A12,A1:A7,0))

E.g.

                A                          B
1   Lot 23 DP789678                23/789678
2   Lot 7 Section 12 DP6789        07/12/89
3   Lot 1 SP 45676                 1/SP45676
4   Common Property Title SP45676  CP/SP45676
5   Volume 7456 Folio 56           7456-56
6   Auto-Consol 5674-78            5674-78
7   Water Access Licence No. 123   WAL123
8    
9    
10   
11   
12  Lot 23 DP789678                23/789678
13  Common Property Title SP45676  CP/SP45676
14  Water Access Licence No. 123   WAL123
15  Water Access Licence No. 123   WAL123

Answer 2

I've got a formula for you. The only problem is that I couldn't fit it into one cell. It is just too big and Excel complains about it. So it has to be done in 2 steps. A1 contains your original text. D1 will contain intermediate result (you can hide this column if you want) E1 will contain the final result.

Formula for D1:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, "Lot ", ""), "Section ", ""), "SP ", "SP"), "Common Property Title", "CP"), "Volume ", ""), "Auto-Consol ", ""), "Water Access Licence No. ", "WAL"), " Folio ","-")

Formula for E1:

=SUBSTITUTE(D1, " ", "/")