Search:

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

Page 1 of 6 1 2 3 4

Search: Search took 0.10 seconds.

  1. 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. Re: Extract 2 words right and 2 words left from a sentence of a cell

    In Google Spreadsheets you can find a DETECTLANGUAGE function.
  3. 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. 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. 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. Re: Deleting Words Based on Number of Words

    Not very complex in VBA either, but I l leave it to somebody else.
  7. 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. Re: Deleting Words Based on Number of Words

    Try this:

    =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",50)),(1+(RIGHT(A2,1)>="A"))*50))
  9. 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. 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. 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. Re: Finding Partial String while using List as a Reference

    You are welcome.
  13. 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. 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. Re: Finding Partial String while using List as a Reference

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

    Where is the sample?
  17. Replies
    22
    Views
    92,741

    Re: Extract Text After Space In String

    Try this:

    =TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,"-"," ")," ",REPT(" ",15)),30,15))
  18. Re: Splitting an address field into separate fields

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

    =IF(SEARCH(B1,A1)>0,"")
  19. 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...
  20. 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...
  21. Re: Splitting an address field into separate fields

    Could you give us some more data to process?
  22. 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. 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. Re: Extracting Last names only from a cell of contacts

    Another version:

    <b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; ...
  25. 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 2 3 4