Excel解析和转换文本 [英] Excel parsing and converting text

问题描述

我需要能够根据以下规则将单元格从一种格式转换为另一种格式:

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 

因此，必须将左侧的内容更改为右侧的项目.例如，如果我有任何看起来像Lot 23 DP789678的单元格，则必须将它们转换为Lot 23 DP789678.我该如何写一个公式来解决以上所有情况?

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?

推荐答案

我为您提供了一个公式.唯一的问题是我无法将其放入一个单元格中.它太大了，Excel抱怨它. 因此，必须分两步完成. A1包含您的原始文本. D1将包含中间结果(如果需要，您可以隐藏此列) E1将包含最终结果.

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.

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 ","-")

E1的公式:

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

这篇关于Excel解析和转换文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！