从字符串excel提取数字 [英] extract number from string excel

问题描述

 工作总计15 / 10015.00％G 
总计工作364 / 1000364.00％Y 
总计工作13 / 4552.86％B 
  

它是一种奇怪的格式，但是它是由我无法控制的系统生成的。

我有兴趣在工作之后提取数字15。目前我正在使用：

  = MID（A2,11，FIND（，A2,11）-11） 
  

并返回15，但最终有一个空格，因此我不能做任何事情的数字作为一个int。

有人可以帮助，所以我只是结束了int，而不是尾随空格？

解决方案

在这种情况下，它只是一个尾随空格，导致它不像一个数字 - 它是从字符串中拉出的 - 这意味着Excel会自动猜测它一个字符串值。

要解决这个公式 - 你可以这样做：

 code> = VALUE（MID（A2,11，FIND（，A2,11）-11））
  

但是，一般来说，您可能会发现使用vba Regex函数来更好地从字符串中拉出数字

I have a string in Excel which is of the following format:

Total Work15 / 10015.00%G
Total Work364 / 1000364.00%Y
Total Work13 / 4552.86%B

Its a bizarre format, but it's being generated by a system I have no control over.

I'm interested in extracting the number 15 after the word "work". At present I am using this:

=MID(A2,11,FIND(" ",A2,11)-11)

and that return "15 " but appears to have a space at the end and I can't therefore do anything with the number as an int.

Can someone help so I only end up with the int and not with the trailing space?

解决方案

it's just a trailing space in this case that's causing it to not act like a number - its that it is pulled from a string - meaning that Excel automatically guesses its a string value.

To fix it in this formula - you could just do :

=VALUE(MID(A2,11,FIND(" ",A2,11)-11))

In general however, you might find that pulling numbers from strings is better using a vba Regex function

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