How do I extract part of a string from Field Info?

[sonseeahray]

How do I go about extracting a portion of the Field info string?

I have a set of Excel files that I need to pull 3 digits from the filename and am trying to do that via the field info string.

All of the files have 12 leading alpha numeric characters. I need to extract positions 10-12 and am not up to speed on reg-ex.

Sample: 1234-678-012... the "678" position numbers are the same with all the files, so I'm sure it's possible to pull the 3 characters to the right of the "678-" string, I don't know reg-ex well enough yet.

[TonyA · Accepted solution]

You don't need regex for this one. If you just need the 10th - 12th characters use a substring function in a formula tool. If the string is in field F1: 

Substring([F1],9,3)

(You need to start from character 9 because string position numbering starts at 0.)

[afv2688 · Accepted solution]

Hello @sonseeahray ,

Based on your example only I used the following tool:

I used this formula => REGEX_Replace([Field1], '^\d+\-(\d+)\-(.*)', '$1')

Based on what you tell you could also use the following => Substring([Field1],10,3)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regards

[sonseeahray]

@TonyA @afv2688 You guys are awesome! I am going to hold on to both of these! Thank you!

[nicoarmisen]

Hi @afv2688 ,

maybe yo can help me hier. I need to "translate" from SQL the following expression:

UPDATE Tmp_Station_Details_All_Slc
SET Main_Station = 'N'
WHERE SUBSTR(STATION_CODE,5,2)='38' AND COUNTRY_CODE ='DE';

I need to extract first the last two characters from the station code and if it is equal to 38 and the country code is DE upgrade Main_Station as 'N'. Is not, then do Nothing.

Thank you in advance!

Nico

[aihnen]

Use formula tool on the column Main_Station with an IF statement with 2 conditions

IF

SUBSTRING(STATION_CODE, 5, 2) = '38'

AND

COUNTRY_CODE = 'DE'

THEN

Main_Station = 'N'

ENDIF

[Linguine]

This is my solution!!

[Linguine]

wait wrong discussion