Trim a string with variable length

[CiaoPaola]

Hi, 

I need to extract some characters within a string

the string is straight after the "PFH." and is 1 char to 3 char long

here are some examples : 

from these examples, I need to capture the "1", the "11" and the "151" strings. 

Is there a way to use the left function and to have a variable length of the string? 

thank you 

Paola

[GaneshBo · Accepted solution]

Hi @CiaoPaola 

I would recommend going the simpler route and using the text to columns tool.

Please see attached.

Best,

Ganesh

[Ben_H]

Hi @CiaoPaola,

You could do it with a slightly convoluted use of substring, something like -

" left(Substring([Data],FindString([Data],".")+1),FindString(Substring([Data],FindString([Data],".")+1),".")) "

Really there are various ways to approach it, another way would be to use Regex, something like this should work in the first instance -

" REGEX_Replace([Data],"(\w*_\w*\.)(\d*)(.*)","$2") "

Regards,

Ben

[CiaoPaola]

Hi GanesBo

thank you very much! 

it works perfectly!

Paola

[CiaoPaola]

Hi Ben_H

thanks a lot for this Regex!

I definitely need some time to get used to reading these regex!

I do not capitulate

thanks again