Skip to main content
  • EN

Hello All,

I have excel spreadsheet that contains attributes like



Trackcomment


O: from 105 to 160


O: from U to S on 117


O: from U to S on 135


O: from S to U on 144


P: from 101 to 111OT


O: from U to S on 146BD

I want to create two new attributes (Columns) that contain middle part of the string (105 or U to S or S to U) and end of a string (160 or 111OT or 146BD) in two different excel columns (Attributes).

I am facing two challenges. I used stringsearcher that extracts the last number of trackcomments but missing if there is any alpha character and the second stringsearcher just extracts if it is U to S or S to U but it is missing if the middle part has a number. Please see the attached screenshot. I would appreciate the help.

GGoraya

Hi @gsgoraya,

One way to do it is using attribute splitters, since the catch is the varation in the data, so I basically make it equal and then apply the same logic.

Hope this helps,

Itayextract-text-from-string-in-two-different-attribut.fmw

Hi @gsgoraya,

One way to do it is using attribute splitters, since the catch is the varation in the data, so I basically make it equal and then apply the same logic.

Hope this helps,

Itayextract-text-from-string-in-two-different-attribut.fmw

Thank you for your help. It works.

GGoraya

Thank you for your help. It works.

GGoraya

Hi @gsgoraya, an excellent solution has been provided already by Itay, but I still think that the StringSearcher may be an option in this case. e.g.

0684Q00000ArN0kQAF.png

Regular Expression:

 

from\s+(.+)\s+p^\s]+\s+(r^\s]+)$
FYI.
Hi @gsgoraya, an excellent solution has been provided already by Itay, but I still think that the StringSearcher may be an option in this case. e.g.

0684Q00000ArN0kQAF.png

Regular Expression:

 

from\s+(.+)\s+p^\s]+\s+(d^\s]+)$
FYI.
Hi @takashi excellent alternative! 

 

I am always hesitant to use the string searcher because of possible data variations and therefore the solution robustness.

 

 

 

Reply


Community Stats

30,123
Posts
114,333
Replies
37,752
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.