Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Satish_Kumar
Helper V
Helper V

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
9 REPLIES 9
Ashish_Mathur
Super User
Super User

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur @Greg_Deckler @vmakhija

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

vmakhija
Post Prodigy
Post Prodigy

@Satish_Kumar

May be I am over simplifying your requirement.

But does something like this won't work?

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

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

 

 

.mid value error.png

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)

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Super User
Super User

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. 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

ERROR.png

I

 

 

 

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

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

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.