Solved

Extract text from string in two different attributes

  • 12 September 2016
  • 4 replies
  • 19 views
G
Badge

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

icon

Best answer by itay 12 September 2016, 22:28

View original

4 replies

itay
Rank
Badge +16

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

G
Badge

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

takashi
Rank
Userlevel 2
Badge +17

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+[^\s]+\s+([^\s]+)$
FYI.
itay
Rank
Badge +16
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+[^\s]+\s+([^\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

28,706
Posts
109,463
Replies
36,549
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.