VBA，excel从字符串中切出一部分 [英] VBA, excel cut a part from a string

问题描述

我想从字符串中获取一部分。我不熟悉VBA或不寻常的excel功能，但我认为也许一个宏或一个VBA代码可以帮助我。

我需要从整个字符串中减少一切电池，除了之后的总计。为了清除这个，我需要总数之后的数字。我不知道excel中的任何功能可以解决我的问题。有没有什么像修剪或某些东西认出总，并给我之后的数字？

示例：
从这个单元格我需要47和另一个47

单元格： Appvg - 总计：47 GB - 免费：20 Gb; rootvg - Total：47 -Free：< 1 GB>

解决方案

两个总计是否一样？如果是这样，你可以使用这个公式（只是将它放在与你的数据相同行上的单元格中，我假设你的单元格是A1）：

= TRIM（MID（A2，SEARCH（total：，A2）+ LEN（total：），2））

如果GB数字将大于两位数，则将2更改为3。您是否有任何其他示例单元格数据可以改进以上？

编辑：如果数字将不同，您可以使用：
= MID（A1，SEARCH（appvg - total：，A1）+ LEN（appvg - total：），2）&，& MID（A1，SEARCH（rootvg-总数：，A1）+ LEN（rootvg - total：），2）

编辑2：所以如果GB大于两位数，比如964 GB，要有一种动态选择的方法。这个数字长达4位数：
= MID（A4，SEARCH（appvg - total：，A4）+ LEN（appvg - total：），SEARCH G，A4）-SEARCH（：，A4）-2）&，& MID（A4，SEARCH（rootvg - total：，A4）+ LEN（rootvg - total：） ，SEARCH（G，A4，SEARCH（G，A4）-SEARCH（：，A4）-1）-SEARCH（：，A4）-2） p>

I would like to get a part from a string. Im not familiar with VBA or unusual excel functions, but I think maybe a macro or a VBA code can help me.

I need to cut down everything from the whole string from the cell, except what is after the Total. To clear this, I need the numbers which stand after the total. I dont know any functions in excel which can solve my problem. Is there something like trim or something which recognize the 'total' and give me the number after that?

Example: From this cell I need the 47 and the other 47

Cell: Appvg - Total: 47 GB - Free:20 Gb; rootvg - Total: 47 -Free: <1 GB>

解决方案

Will the two Totals always be the same? If so, you can use this formula (just put this in a cell on the same row as your data, I am assuming your Cell is A1):

=TRIM(MID(A2,SEARCH("total: ",A2)+LEN("total: "),2))

If the GB numbers will ever be larger than two digits, change the "2" to "3". Do you have any other example cell data to work off to improve the above?

Edit: If the numbers will be different, you can use this: =MID(A1,SEARCH("appvg - total: ",A1)+LEN("appvg - total: "),2)&", "&MID(A1,SEARCH("rootvg - total: ",A1)+LEN("rootvg - total: "),2)

edit 2: I am working on the formula so if the GB are greater than two digits, such as 964 GB, to have a way to dynamically pick that out. This seems to work up to numbers 4 digits long: =MID(A4,SEARCH("appvg - total: ",A4)+LEN("appvg - total: "),SEARCH(" G",A4)-SEARCH(":",A4)-2)&", "&MID(A4,SEARCH("rootvg - total: ",A4)+LEN("rootvg - total: "),SEARCH(" G",A4,SEARCH(" G",A4)-SEARCH(":",A4)-1)-SEARCH(":",A4)-2)

这篇关于VBA，excel从字符串中切出一部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！