Substring Formula Pulling Wrong Value

[cconsolo]

Trying to get the below formula tool to create this new column (3rd digit of WO) that is supposed to be the 3rd digit of the lot data inputted in that line. Sometimes there are letters, other times there are numbers. Sometimes it is pulling the 3rd digit, other times it pulls the second based off the data.

Sample Lot Data:

TL2N1

BTMS1

BLTMO

04691

Needs to pull 3rd value of all of these and place in the new column, 3rd digit of WO.

EX output needed:

2

M

T

6

[Felipe_Ribeir0 · Accepted solution]

Hi @cconsolo 

For your sample input, the expected output seems fine with Substring([Field1],2,1). Maybe you have spaces at the beggining of some cells, try to apply the Trim(Field1) function before of the substring

[cconsolo · Accepted solution]

Hi thank you, how do I add the Trim feature in? It's showing unmatched error, also some of the Lot's have more than 6 characters. Attached is the full lot list that I need to just pull the 3rd character.

[Felipe_Ribeir0]

Hi @cconsolo 

You can do it like this

For this input sample that you shared, what Lots are giving you the wrong 3 character?