Excel 解析和转换文本

发布于 2024-08-02 10:15:16 字数 634 浏览 7 评论 0原文

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

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。我如何编写一个公式来针对上述所有情况执行此操作？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

缘字诀 2024-08-09 10:15:16

我有一个公式给你。唯一的问题是我无法将它放入一个单元格中。它太大了，Excel 抱怨它。
所以必须分两步完成。
A1 包含您的原始文本。
D1 将包含中间结果（如果需要，您可以隐藏此列）
E1将包含最终结果。

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

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

回复收藏 0 原文

稍尽春風 2024-08-09 10:15:16

您可以使用 MATCH 和 INDEX 函数。假设属性描述和作品集标识符分别位于A1:A7和B1:B7列中，并且要替换的字符串位于A12中，则令B12为：

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

例如

                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

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

回复收藏 0 原文

~没有更多了~