Forum Discussion

Copper Contributor

Jan 02, 2023

Solved

MID and FIND

I need your help. Using just MID and FIND function I need to retrieve just post code e.g 2007 from:

310 Wattle StUltimo, 2007, NSW

Thanks

Lukasz

Formulas and Functions

Lorenzo
Jan 03, 2023
LucasP40
But I need to use only 1 MID and 1 FIND function to get this result ??? What is/are your constraint(s)?

Based on your single example, this could be:
=MID(A1, FIND(", ", A1)+2, 4)

6 Replies

mtarler
Silver Contributor
Jan 03, 2023
how about
=mid(a1, find(",",a1)+1, find(",",a1,find(",",a1)+1)-find(",",a1)-1)
but using the new formulas would be easier:
=INDEX(TEXTSPLIT(A1,","),2)
- LucasP40
  Copper Contributor
  Jan 03, 2023
  mtarler
  thanks for quick reply.
  But I need to use only 1 MID and 1 FIND function to get this result. My closest result is “, 2007” with “=MID(A1,FIND(“, “,A1),6” but can’t get rid off “, “ before 2007.
  Any thoughts?
  Thanks in advance
  - Lorenzo
    Silver Contributor
    Jan 03, 2023
    LucasP40
    But I need to use only 1 MID and 1 FIND function to get this result ??? What is/are your constraint(s)?
    
    Based on your single example, this could be:
    =MID(A1, FIND(", ", A1)+2, 4)

Resources