# Search:

Type: Posts; User: István Hirsch; Keyword(s):

Page 1 of 6 1

1. ## Thread: Count varied number string in a cell

by István Hirsch
Replies
27
Views
920

### Re: Count varied number string in a cell

Or you can try this formula (confirm with Ctrl -Shift - Enter):

=SUM(IF((MID(CHAR(10)&A2,ROW(\$1:\$3000),1)=CHAR(10))*(ISNUMBER(-MID(CHAR(10)&A2,ROW(\$1:\$3000)+1,1)))=1,1,0))
2. ## Thread: Extract 2 words right and 2 words left from a sentence of a cell

by István Hirsch
Replies
10
Views
610

3. ## Thread: Extract multiple 5-digit numbers from alphanumeric text

by István Hirsch
Replies
11
Views
1,088

### Re: Extract multiple 5-digit numbers from alphanumeric text

Thanks, this is my corrected version:

=IFERROR(TEXT(LARGE(IF(NOT(ISNUMBER(--(MID(" "&\$A2&" ",ROW(\$1:\$200),1))))*ISNUMBER(--(MID(" "&\$A2&" ",ROW(\$1:\$200)+1,5)&"."))*NOT(ISNUMBER(--(MID(" "&\$A2&"...
4. ## Thread: Extract multiple 5-digit numbers from alphanumeric text

by István Hirsch
Replies
11
Views
1,088

### Re: Extract multiple 5-digit numbers from alphanumeric text

It seems my formula in post #8 does not extract the 5-digit number if it is the first substring of the string. Corrected version in cell C1:

=IFERROR(TEXT(LARGE(IF(NOT(ISNUMBER(--(MID("...
5. ## Thread: Extract multiple 5-digit numbers from alphanumeric text

by István Hirsch
Replies
11
Views
1,088

### Re: Extract multiple 5-digit numbers from alphanumeric text

If you want to extract more than 3 numbers with a formula, give this a try. Enter the formula in C1 with Ctrl - Shift - Enter, then copy across and down. Then enter the formula in b1 and copy down....
6. ## Thread: Deleting Words Based on Number of Words

by István Hirsch
Replies
9
Views
454

### Re: Deleting Words Based on Number of Words

Not very complex in VBA either, but I l leave it to somebody else.
7. ## Thread: Deleting Words Based on Number of Words

by István Hirsch
Replies
9
Views
454

### Re: Deleting Words Based on Number of Words

(RIGHT(a2,1)>="A") is TRUE (or 1) if the last character of the string is a letter, otherwise zero. So, in the former case 1+(right(a2,1)>="A") is 2, otherwise 1. It means that we go back from the...
8. ## Thread: Deleting Words Based on Number of Words

by István Hirsch
Replies
9
Views
454

### Re: Deleting Words Based on Number of Words

Try this:

=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",50)),(1+(RIGHT(A2,1)>="A"))*50))
9. ## Thread: Separate names and email id

by István Hirsch
Replies
11
Views
472

### Re: Separate names and email id

Thanks Peter, your note shed light on my long-term mistake: I thought that Google Sheets is based on Excel 2016 (I haven’t the latter), now I see that they are totally different. The functions I...
10. ## Thread: Separate names and email id

by István Hirsch
Replies
11
Views
472

### Re: Separate names and email id

Or if you have Excel 2016, you can try this:

=REGEXREPLACE(A2;"( <.*?>)+";"")

=SUBSTITUTE(SUBSTITUTE(REGEXREPLACE("ß>"&A2;(">.*?<");", ");"ß, ";"");">";"")
11. ## Thread: Need help extracting word in string that must be combination of numbers and letters

by István Hirsch
Replies
8
Views
455

### Re: Need help extracting word in string that must be combination of numbers and letters

And how many alphanumeric texts may occur per cell (maximum)?
12. ## Thread: Finding Partial String while using List as a Reference

by István Hirsch
Replies
10
Views
388

### Re: Finding Partial String while using List as a Reference

You are welcome.
13. ## Thread: Separate names and email id

by István Hirsch
Replies
11
Views
472

### Re: Separate names and email id

For example, do you want the first name in the next column, the email next to it, then the second name in the next column and so on, or what?
14. ## Thread: Finding Partial String while using List as a Reference

by István Hirsch
Replies
10
Views
388

### Re: Finding Partial String while using List as a Reference

Or try this:

=LOOKUP(1,-SEARCH(D\$2:D\$4,A2),\$D\$2:\$D\$4)

If the reference list contains complex colours, for example dark-green and green, put the reference list in the descending order of colour...
15. ## Thread: Finding Partial String while using List as a Reference

by István Hirsch
Replies
10
Views
388

### Re: Finding Partial String while using List as a Reference

Please insert the desired result (manually) into the table above.
16. ## Thread: Finding Partial String while using List as a Reference

by István Hirsch
Replies
10
Views
388

### Re: Finding Partial String while using List as a Reference

Where is the sample?
17. ## Thread: Extract Text After Space In String

by István Hirsch
Replies
22
Views
92,741

### Re: Extract Text After Space In String

Try this:

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,"-"," ")," ",REPT(" ",15)),30,15))

by István Hirsch
Replies
14
Views
601

### Re: Splitting an address field into separate fields

It seems in Sheet 1 cell D1 this formula is better:

=IF(SEARCH(B1,A1)>0,"")

by István Hirsch
Replies
14
Views
601

### Re: Splitting an address field into separate fields

Exclamation mark was to show items where the name of the city belonging to the given zip code and the one in the address string is different. Now these issues are marked by the #VALUE error in Sheet...

by István Hirsch
Replies
14
Views
601

### Re: Splitting an address field into separate fields

I would download the list of cities in California along with their zip codes (Sheet 2), try to find the city according to the found zip code, and would check if the city is really contained in Sheet...

by István Hirsch
Replies
14
Views
601

### Re: Splitting an address field into separate fields

Could you give us some more data to process?
22. ## Thread: Extracting left most numbers from excel

by István Hirsch
Replies
12
Views
406

### Re: Extracting left most numbers from excel

If you need a formula solution give this a try:

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt;...
23. ## Thread: Extract part of cell based on condition

by István Hirsch
Replies
5
Views
296

### Re: how to extract part of cell based on condition ?

Another approach without CSE:

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff;...
24. ## Thread: Extracting Last names only from a cell of contacts

by István Hirsch
Replies
10
Views
715

### Re: Extracting Last names only from a cell of contacts

Another version:

25. ## Thread: INDEX MATCH MAX IF Function without the use of an array

by István Hirsch
Replies
19
Views
871

### Re: INDEX MATCH MAX IF Function without the use of an array

Try this. Enter the date and silo into cell B1, then the formula in B2 and copy down. You get the maximum for that date and silo in C2.

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0"...
Results 1 to 25 of 150
Page 1 of 6 1