Forum Discussion

Satish_Kumar's avatar
Satish_Kumar
Helper V
8 years ago

how to fetch string from paragraph

Hi All,

I just want to know the to extract a string of series from a pragraph.

 

for example:

paragarph 1

 

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx AW:1345578299929909 date: 15-09-2017(line 2)

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx loan Amount:22292 USD(line 10)

xxxxxxxxxxxxxxxxxxxxxxxxxxxxx

xxxxxxxxxxx

 

 

 

paragarph 2:

 

 

xxxxxxxxxxxxxxxx AW:1334499929909 date: 25-09-2017(line 3)

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx loan Amount:21192 USD(line7)

xxxxxxxxxxxxxxxxxxxxxxxxxxxxx

 

 

 

So i have to extract these string from a paragarph where there exitance in paragraph is not fixed.

they might be present any where in the paragarph.

 

So, please help me out of this problem.................

 

 

Regards,

Satish
Need Help

9 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    So, generally you use FIND or SEARCH to locate a keyword and then feed this information into RIGHT, MID, or LEFT to extract the information that you want. 

    • Satish_Kumar's avatar
      Satish_Kumar
      Helper V
      8 years ago

      Greg_Deckler

       

      This paragarph is present in a single row and i have 7K line item named with block4 column.

      So can you suggest me that what formula should i use to find my text and extarct the string.

      where my string place is not fixed.

       

       

      regards,

      Satish

    • Satish_Kumar's avatar
      Satish_Kumar
      Helper V
      8 years ago

      I

       

       

       

      I am not able to put search function on the column block4

      • Satish_Kumar's avatar
        Satish_Kumar
        Helper V
        8 years ago
        Hi All,

I just want to know the to extract a string of series from a pragraph.

 

for example:

paragarph 1

 

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx AW:1345578299929909 date: 15-09-2017(line 2)

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx loan Amount:22292 USD(line 10)

xxxxxxxxxxxxxxxxxxxxxxxxxxxxx

xxxxxxxxxxx

 

 

 

paragarph 2:

 

 

xxxxxxxxxxxxxxxx AW:1334499929909 date: 25-09-2017(line 3)

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx loan Amount:21192 USD(line7)

xxxxxxxxxxxxxxxxxxxxxxxxxxxxx

 

 

 

So i have to extract these string from a paragarph where there exitance in paragraph is not fixed.

they might be present any where in the paragarph.

 

So, please help me out of this problem.................

 

 

Regards,

Satish
  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Satish_Kumar

    May be I am over simplifying your requirement.

    But does something like this won't work?

    New = SUBSTITUTE(Table2[String],"x","")

    • Satish_Kumar's avatar
      Satish_Kumar
      Helper V
      8 years ago

      AnonymousGreg_Deckler

       

      Hi I am getting this error"An argument of function 'MID' has the wrong data type or has an invalid value."

       

      while using MID function

       

       

      .

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        8 years ago

        I think you need to read the documentation on these functions more closely. 

         

        SEARCH

        https://msdn.microsoft.com/en-us/library/ee634235.aspx

         

        You only need 2 parameters, the text to find and where to find it. The last two are optional, where to start (number) and what to return if not found.

         

        MID

        https://msdn.microsoft.com/en-us/library/ee634548.aspx

         

        You need three parameters, the text you want to grab a string from, where to start and how many characters to get

         

         

        So, let's say that you want to pull the phrase "DAX is fun" from some text but you don't know where it is located in the text of [Column4]. You would use something along the lines of:

         

        Column = MID([Column4],SEARCH("DAX is fun",[Column4]),10)
    • Satish_Kumar's avatar
      Satish_Kumar
      Helper V
      8 years ago

      Ashish_Mathur Greg_Deckler Anonymous

      Hi,

      i hope you all are doing well!!!

       

      as per the need i develop a dump data for my query....

       

      F79:WE INVITE REFERENCE TO YOUR LETTER ADVISING US 
                YOUR OFFER FOR EXTENDING BUYER'S CREDIT TO THE 
                EXTENT OF USD 4,294,839.64 FOR THE PERIOD FROM  
                03.01.2017 TO 23.05.2017 (140 DAYS) ON ACCOUNT 
                OF OUR CONSTITUENTS  ABC COMPANY (IMPORTER 
                EXPORTER CODE NO:- 030219854) AT 6 M LIBOR+70 
                BASIS POINTS, DETAILED AS UNDER,
                1)IMPORTER :-ABC COMPANY
                2)EXPORTER:-PAN INDIA COMPANY
                3)PERIOD :-03.01.2017 TO 23.05.2017
                4)PRODUCT:-FOODITEMS
                5)COUNTRY OF ORIGIN:-U.A.E.
                6)DATE OF SHIPMENT:-06.12.2016,07.12.2016
                7)PORT OF LOADING:-U.A.E.
                8)PORT OF DISCHARGE:-PUNE,INDIA
                9)FINAL DESTINATION OF GOODS:DELHI
                10)CARRIER NAME:-JET AIRWAYS
                11)VESSEL NAME:-3SRD
                12)LC EXPIRY DATE:NOT UNDER LC,COLLECTION BASIS
                13)LC ISSUING BANK:NOT APPLICABLE
                14)COUNTRY OF IMPORTER:-INDIA
                15) COUNTRY OF EXPORTER:-U.A.E.
                WHILE ACCEPTING YOUR   SAID OFFER, WE CONFIRM 
                AND IRREVOCABLY UNDERTAKE THAT:
                WE UNDERTAKE TO REPAY THE PRINCIPAL AMOUNT 
                FINANCED PLUS INTEREST AND CHARGES WITHOUT ANY 
                DEDUCTIONS REGARDLESS OF WHETHER THEY ARE 
                WITHHOLDING TAXES OR CORRESPONDENCE CHARGES,ETC. 
                THE DOCUMENTS UNDER THE LC HAVE BEEN ACCEPTED BY 
                US, THE APPLICANT IS OUR CUSTOMER, WE ARE 
                SATISFIED WITH THE CONDUCT OF THEIR ACCOUNT
      F79:WE INVITE REFERENCE TO OUR TELEPHONIC 
                CONVERSATION DATED 07.03.2017 ADVISING US 
                YOUR OFFER FOR EXTENDING BUYER'S CREDIT TO 
                THE EXENT OF USD 7,296,584.62 FOR THE PERIOD 
                FROM 07.03.2017 TO 22.02.2018 (352 DAYS) ON 
                ACCOUNT OF OUR CONSTITUENT,M/S ABC COMPANY
                AT 12 M LIBOR PLUS 86 BPS.WHILE ACCEPTING YOUR
                SAID OFFER, WE CONFIRM AND UNDERTAKE THAT:
                1.NECESSARY APPROVALS FROM COMPETENT AUTHORITY AS
                PER EXCHANGE CONTROL/FEMA REGULATIONS THAT ARE IN
                FORCE HAVE BEEN DULY OBTAINED/COMPLIED WITH.
                2.NO OTHER FINANCIAL LIABLITY IS OUTSTANDING IN
                RESPECT OF THE SAID TRANSACTION.
                3.DOCUMENTS HAVE ALREADY BEEN ACCEPTED.
                4.WE WILL MAKE AVAILABLE TO YOU AND/OR YOUR
                AUTHORISED REPRESENTATIVES, ALL THE DOCUMENTS
                RELATING TO THIS TRANSACTION FOR EXAMINATION/
                VERIFICATION.
                5.THE PARICULARS REGARDING IMPORTS ARE: NO:
                3731/ABC COMAPNY/16.
                NAME AND ADDRESS : ABC COMPANY,146/A,GROUND 
                OF THE IMPORTER  : FLOOR,SPECIAL ECONOMIC
                NAME AND ADDRESS XXANCC,13B,WORLD 
                OF EXPORTER      :YYSSSF DFGC 
                                 :CENTRAL,HONGKONG
                                 :MONALISA,UNIT 7,7/F
                                 :ZONE2, COMMERCIAL CENTRE,
                                 :LINE ROAD CENTRE
                                 :ABC TOWER.
       F79:PLEASE REFER TO YOUR TELEPHONE CONVERSATION
                NAME OF IMPORTER/BC APPLICANT:ABC DETAIL
                NAME OF BENEFICIARY:LASSC LTD PVT
                AMOUNT OF LOAN:USD 10,394,238.26
                TENOR:200 DAYS 
                RATE OF INTEREST :12%
                AWB NO/BL NO. AND DATE:160-3465-14582,589-9221-5803
                ,589-1104-3436 DTD 10.04.2017,09.04.2017
                FLIGHT NAME:CAS63,9DF43.
                DESCRIPTION OF GOODS :FOOD
                DATE OF DISBURSEMENT :12.04.2017
                DUE DATE  :03.04.2018
                SHIPMENT:FROM HONG KONG,TO UAE
                PORT OF LOADING AND DISCHARGE:HONG KONG,UAE
                SHIPMENT DATE:10.04.2017,09.04.2017
                NATURE OF GOODS:NA
      R NOSTRO A/C  NO.:2234-4454-2234 
       

       

      the heighlighted field i need to extract from each rows.

      and it is quite possible that the item which i need to extract from each rows may now present in every rows as there presence in not certain for each rows .

      So what i need to do develop a query for these items if they are present any where in the cloumn they will get picket by my query.

       

      Regards,

      Satish Kumar