Hi-I am trying to parse out a string using Fi...

(Eli) #1

Hi-I am trying to parse out a string using Find and Left expression. I am having trouble grabbing the characters in the middle. For example, if this is my string (110054:06MAR2019-USA_NEW) how would I parse out the middle portion?

(Eli) #2

+Steve Coile

Thanks for the reply and yes I am trying to parse out 06MAR2019

I tried your expression but nothing returned although the expression was valid. I then tried this =RIGHT(([Scan Text], FIND(":",[Scan Text])), LEFT([Scan Text], FIND("-",[Scan Text]))) but I cant get the FIND associated with RIGHT to work. Any suggestions on Syntax?

(Stephen Mattison) #3

Hope this helps you, works for me. Pull Zipcode out of this Address 1208 MOULTRIE RD, ALBANY, GA 31705, USA =RIGHT(F2,(len(F2)(find(", ",F2))))

Removes Street =RIGHT(G2,(len(G2)(find(", ",G2))))

Removes City =LEFT(H2,FIND(",",H2)-1) Removes , USA =RIGHT(I2,5)

Removes State

(Steven Coile) #4

The “middle portion” being 06MAR2019?

left(right([Column], (find([Column], “:”) + 1)), (find([Column], “-”) - find([Column], “:”) - 1))

Which is:

right([Column], (find([Column], “:”) + 1)) extracts the text in the [Column] column beginning at (right(…)) the character immediately following (find(…) + 1) the colon (find(…, “:”)), which would be 06MAR2019-USA_NEW.

left(…, (find([Column], “-”) - find([Column], “:”) - 1)) then extracts from that result (06MAR2019-USA_NEW) the first so-many characters (left(…)) equal to the number of characters between the colon and the minus ((find(…, “-”) - find(…, “:”) - 1)) in the original text ([Column]), giving 06MAR2019.

I haven’t tested this; it’s off the top of my head. Test accordingly.