-1

I have a file which contains a list of data. In each cell is a name and number and a date the date is either mm/yy or mm-yy or mm-yyyy etc. (never the day just month and year) The number I need is always going to be greater than 5 characters. Is there a way that I can get just the number from the string

  • xx company holding - 96923432 -02-22. (number required 96923432)
  • yy Company (HOLDINGS) LTD - 131002204 - 02/2023 (number required 131002204)
  • ab HOLDINGS LIMITED / 115472907 / Feb-23 (number required 115472907)
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
Mike Dray
  • 133
  • 1
  • 1
  • 9
  • You have not shown that you have tried anything. I suggest you write VBA code to use Regular Expressions in a User Defined Function. See [How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops](https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops) – Ron Rosenfeld Feb 15 '23 at 20:21

2 Answers2

2

... prior removed

=========UPDATE=========

This formula will work for you, which splits your data by space, then converts to a number and then extracts the max. Adjust as needed if you have occasions where you may not have a number greater than 5 by wrapping with an IF().

=MAX(IFERROR(NUMBERVALUE(TEXTSPLIT(A2," ")),0))

enter image description here

Isolated
  • 5,169
  • 1
  • 6
  • 18
0

This is interesting since you use 2 different delimiters. However, no worries you can simply use the following to capture both instances. If you have more possible delimiters simply just add them between the {} in both textbefore and textafter functions. Here is an example of the equation:=TEXTBEFORE(TEXTAFTER(A2, {"-","/"}), {"-","/"})

This should work for you then if you want to return nothing if output is less than 5. =IF(LEN(TEXTBEFORE(TEXTAFTER(A1,{"-","/"}),{"-","/"}))>5,TEXTBEFORE(TEXTAFTER(A1,{"-","/"}),{"-","/"}),"")

keef2
  • 23
  • 7
  • thanks sorry yes this is just an example of the data. The number could be between any number of things. I might be between .. or they might use nothing and just use a space before and after. The key for me is to be able to extract any number that is greater than a set number of characters. So any number that is greater than 5 characters for example. – Mike Dray Feb 15 '23 at 17:24
  • sorry I think the examples are misleading. There is no delimiter. These values are created free text by different users. so it could be xx company holding - 96923432 02-22 or it could be xx company holding 96923432 02-22 etc. The only unique factor is that it is a number that is greater than 5 characters – Mike Dray Feb 15 '23 at 17:56