如何将具有字母数字值的Excel单元格中的文本导出到另一列? [英] How do I export text from an excel cell with alphanumeric values into another column?

问题描述

我在B列中有几个单元格条目.它们看起来类似于:

I have several cell entries in column B. They look similar to this:

1050670||Target Optical  4226||6132||7132
1051752||Wal-Mart Vision Ctr  305095||6132||7132
1052470||Wal-Mart Vision Ctr  301891||6132||7132
1054354||Naval Ambulatory Care Ctr||6132||7132

我需要一个仅提取包含名称的文本的公式.理想情况下，它看起来只剩下我一个:

I need a formula that will extract only the text containing the name. Ideally it would look leave me with only:

Target Optical
Wal-Mart Vision Ctr
Wal-Mart Vision Ctr
Naval Ambulatory Care Ctr

我们非常感谢您的帮助.

Any help is GREATLY appreciated.

推荐答案

如果要忽略文本"伪字段中的数字，则必须仔细解析ASCII 48-57内字符的拆分值.

If you want to disregard the numbers within the Text pseudo-field, you will have to parse the split-out value closely for characters within ASCII 48-57.

B1中的丑陋公式是

=TRIM(LEFT(MID(A1, FIND("||", A1)+2, FIND("¶", SUBSTITUTE(A1, "||", "¶", 2))-FIND("||", A1)-2)&0, MIN(INDEX(ROW(INDIRECT("1:"&LEN(MID(A1, FIND("||", A1)+2, FIND("¶", SUBSTITUTE(A1, "||", "¶", 2))-FIND("||", A1)-2)&0)))+((CODE(MID((MID(A1, FIND("||", A1)+2, FIND("¶", SUBSTITUTE(A1, "||", "¶", 2))-FIND("||", A1)-2)&0),ROW(INDIRECT("1:"&LEN(MID(A1, FIND("||", A1)+2, FIND("¶", SUBSTITUTE(A1, "||", "¶", 2))-FIND("||", A1)-2)&0))),1))<48)+(CODE(MID(UPPER(MID(A1, FIND("||", A1)+2, FIND("¶", SUBSTITUTE(A1, "||", "¶", 2))-FIND("||", A1)-2)&0),ROW(INDIRECT("1:"&LEN(MID(A1, FIND("||", A1)+2, FIND("¶", SUBSTITUTE(A1, "||", "¶", 2))-FIND("||", A1)-2)&0))),1))>57))*1E+99,,))-1))

根据需要填写.看起来很糟糕，但中等数组公式的计算负荷会使它变得相形见but，但是INDIRECT确实使它易失，因此获取剥离后的值并将复制，粘贴特殊值"重新删除以删除公式.

Fill down as necessary. As bad as it looks, the calculation load on a medium array formula would dwarf it but the INDIRECT does make it volatile so get your stripped values and Copy, Paste Special Values back to remove the formulas.

这篇关于如何将具有字母数字值的Excel单元格中的文本导出到另一列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！