how to fetch string from paragraph

[Satish_Kumar]

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

[Ashish_Mathur]

Hi,

Share some data to work with.  Show the expected result as well.

[Satish_Kumar]

@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

[Anonymous]

@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]

@Anonymous@Greg_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]

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)

[Greg_Deckler]

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]

I

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

[Satish_Kumar]

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

[Satish_Kumar]

@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