从Excel单元格中提取数据 [英] Extract data from Excel cell

问题描述

我在列A中的Excel表中的数据格式如下，并将其解压缩到不同的列

 列A 
 XXXXX（YYYYY（ABCDEF）0000000000 0000000000）
 XXXXX（YYYYY 0000000000）
  

必需的结果

  A | B | C | D 
 XXXXX | YYYYY | 0000000000,0000000000 | ABCDEF 
 XXXXX | YYYYY | 0000000000,0000000000 | 
  

想要为这两种条件使用一个公式，因为数据无法排序

解决方案

在B1位置

  = TRIM（LEFT （A1，FIND（（，A1）-1））
  

字符左边的第一个（TRIM功能删除多余的空间和其他一些特殊字符，你可能也可能不想使用修剪，如果你不减少-1到-2或-3等，直到你在

在C1中

  = TRIM（MID（A1，FIND（ （，A1）+ 1，FIND（ （，A1，FIND（ （，A1）+1） - 查找（ （，A1 ）-1））
  

有点复杂，很多重复性使得很难阅读，基本上我们找到第一个（，然后转到右边的1个字符并开始拉出字符，以确定要拉取的字符数，第二个 （ 被找到。这是通过在D1中找到第一个（之后的（。

  = SUBSTITUTE（TRIM（MID（A1，FIND（），A1）+ 1，LEN（A1）-FIND（），A1）-1））， ，）
  

与之前的示例中所做的相似，第一个）和我们查看字符串长度的字符数，并减去第一个）的位置。一旦下降到空格分隔的数字，则使用substitue命令来替换空白。

在E1

  = MID（A1，FIND（（，A1，FIND（（，A1）+1）+ 1，FIND（），A1） FIND（（，A1，FIND（（，A1）+1）-1）
  

这只是一个非常丑的重复，我们之前做过的只是找到不同的起点和终点来翻译文本。

你需要执行以下操作才能所有你的信息回到A1，你需要粘贴价值或你的公式将指出所有的错误的东西，它通常只是看起来真的很糟糕。

1. 选择B1到E1，然后根据需要复制多行。


2. 选择B1到E的最后一行，然后复制。


3. 粘贴到顶部粘贴值或粘贴到A1


4. 根据您决定粘贴的位置删除A1或E1。

如果这是您在ar上执行的操作，则此过程可以记录在宏中例如：

I have data in excel sheet in Column A in format below and want to extract it to different columns

                Column A
XXXXX (YYYYY (ABCDEF) 0000000000 0000000000)
XXXXX (YYYYY 0000000000)

Result Required

  A   |   B   |           C           |    D
XXXXX | YYYYY | 0000000000,0000000000 | ABCDEF
XXXXX | YYYYY | 0000000000,0000000000 | 

Want to use one single formula for both the conditions as data cannot be sorted

解决方案

in B1 place

=TRIM(LEFT(A1,FIND("(",A1)-1))

This formula takes the all the character to the left of the first "(". The TRIM function removes excess space and a few other special characters. You may or may not want to use trim. if you do not decrease -1 to -2 or -3 etc until you have the right number of characters returned with no space at the end.

in C1

=TRIM(MID(A1,FIND("(",A1)+1,FIND("(",A1,FIND("(",A1)+1)-FIND("(",A1)-1))

Little trickier and a lot of repetitiveness that makes it hard to read. basically we find the first "(" and go 1 character to the right of that and start pulling characters. in order to determine the number of characters to pull, the position of the second "(" was found. this was done by finding the first "(" after the first "(".

in D1

=SUBSTITUTE(TRIM(MID(A1,FIND(")",A1)+1,LEN(A1)-FIND(")",A1)-1))," ",",")

Same as what we did in the previous examples in terms of pulling text after finding a starting point of first ")" and the number of character we looked at the length of the string and subtracted the postion of the first ")". Once it was down to numbers separated by a space, substitue command was used to replace the space with a coma.

in E1

=MID(A1,FIND("(",A1,FIND("(",A1)+1)+1,FIND(")",A1)-FIND("(",A1,FIND("(",A1)+1)-1)

This is just a really ugly repeat of what we did before just finding different start and end points to rip the text with.

You need to do the following in order to all you information back into A1. You need to paste value or your formulas will be pointed at all the wrong things and it will generally just look really bad.

1. Select B1 to E1 and copy down as many rows as you need.
2. Select B1 to your last row in E and copy.
3. Paste right over top with paste values or paste in A1
4. Delete A1 or E1 depending where you decided to paste.

This process can be recorded in a macro if this is something you do on a regular basis.

这篇关于从Excel单元格中提取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！