如何用多个下划线和不同长度的值分隔文本 [英] how to separate text with multiple underscores and varying length of values

问题描述

a1 = ac_tree_birch_NewYork_ext

a2 = bc_animal_dog_Washington_des

如何将单元格中的文本分开_，因为单元格值的长度是不同的。我想使用一个公式，而不是文本到列。

谢谢

解决方案

使用

a1=ac_tree_birch_NewYork_ext

a2=bc_animal_dog_Washington_des

How do I separate the text in the cells by the "_", since there is varying length of the cell values. I would like to use a formula, and not text to columns.

Thanks

解决方案

Use the SUBSTITUTE function to change all underscores (e.g. CHAR(95)) to a large number of spaces (typically the entire length of the original string) and peel out the padded pieces with the MID function. Finish off with TRIM and an IFERROR 'wrapper'.

In B1 as,

=IFERROR(TRIM(MID(SUBSTITUTE($A1, CHAR(95), REPT(CHAR(32), LEN($A1))), (COLUMN(A:A)-1)*LEN($A1)+1, LEN($A1))), TEXT(,))

Fill both right and down.

  

这篇关于如何用多个下划线和不同长度的值分隔文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！