Excel Formula Extract any number greater than x charters from a string

0 votes

I have a file with a list of data in it. Each cell has a name, a number, and a date, which is either in the format mm/yy, mm-yy, mm-yyyy, etc. (just the month and year, never the day) The quantity I require will never be less than five characters. Is it possible for me to extract 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)
Feb 16, 2023 in Others by Kithuzzz
• 38,010 points
310 views

1 answer to this question.

0 votes

Given that you employ two separate delimiters, this is intriguing. But don't worry, you can easily use the next method to record both instances. Simply add additional delimiters between the the textbefore and textafter functions if you have any available. A sample of the equation is shown below.

=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,{"-","/"}),{"-","/"}),"")
answered Feb 16, 2023 by narikkadan
• 63,720 points

Related Questions In Others

0 votes
1 answer

(Excel) If cell is greater than <condition> then minus <number>

The IF function to calculate B5 (amount ...READ MORE

answered Nov 7, 2022 in Others by narikkadan
• 63,720 points
1,056 views
0 votes
1 answer

Excel SUMIFS() using string with greater/less than symbols

Put an = in front to let Excel know ...READ MORE

answered Nov 20, 2022 in Others by narikkadan
• 63,720 points
471 views
0 votes
1 answer

Filter/Extract a text from excel sheet having similar values

ISNUMBER(SEARCH("Maria,",SUBSTITUTE(A1:A4,"]",",")))  shows TRUE if Maria is found and false if it would ...READ MORE

answered Jan 9, 2023 in Others by narikkadan
• 63,720 points
242 views
0 votes
1 answer

Excel: Is it possible to reorder the data in 2 columns to match up if they have a certain number of characters / a string in common?

Try this: =LET(files,A1:A4, URLs,B1:B4, f,BYROW(files,LAMBDA(r,TEX ...READ MORE

answered Jan 21, 2023 in Others by narikkadan
• 63,720 points
357 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
996 views
0 votes
1 answer

ImportError: openpyxl is required for loading excel format files

Forget the PsychoPy complications for the time ...READ MORE

answered Oct 3, 2018 in Python by Priyaj
• 58,090 points
913 views
0 votes
1 answer

In Blue Prism how to split excel column data into TWO columns

This is how I am doing it. Dim ...READ MORE

answered Oct 15, 2018 in RPA by Priyaj
• 58,090 points
4,199 views
0 votes
1 answer

Excel Power Query: Using List.MatchAny on a column value

try this. let TableA = ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,160 points
4,005 views
+1 vote
1 answer

Excel or Google formula to count occurrences of an 8-digit number within a text string

To match an eight-digit number, you may ...READ MORE

answered Dec 24, 2022 in Others by narikkadan
• 63,720 points
766 views
0 votes
1 answer

Is there a maximum number of formula fields allowed in Excel (2010)

See http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010073849.aspx for limits on specs it doesn't indicate ...READ MORE

answered Sep 30, 2022 in Others by narikkadan
• 63,720 points
557 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP