Excel formula to extract text between characters

pratheesh1983

Board Regular
Joined
Aug 13, 2015
Messages
55
Office Version
  1. 365
Platform
  1. Windows
I've a payment data which has a multiple field tags, each field tag have Some info. Eg.
":AT01: ABCDEFG :AT05: 123XYZMU :AT08: AMIT Bangalore India :AT13: Windows Exp. "
The Field ID are named as :AT0..: I need a formula to extract details mentioned in specific Field tag, eg. If I need to extract data for field tag AT13, then the extracted data should be "Windows Exp", or if I need to extract data from :AT01: then it will be "ABCDEFG". Iam looking for a formula if will help me to extract this details from a specific field tag. Could any one please help me with this?
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Please provide a sample employing XL2BB showing your sample data and your expected results.

Difficult to determine whether your data is in one cell or multiple and how you indicate what the criteria is.
 
Upvote 0
Please provide a sample employing XL2BB showing your sample data and your expected results.

Difficult to determine whether your data is in one cell or multiple and how you indicate what the criteria is.

Please provide a sample employing XL2BB showing your sample data and your expected results.

Difficult to determine whether your data is in one cell or multiple and how you indicate what the criteria is.
 
Upvote 0
Hello Alan,

The entire data will be in one cell from column A. Criteria to extract data will be based on the Field Tag mentioned in Column B. For Better understanding I have created the sample data below.

DATA (Column A)FIELD ID DATA WHICH NEEDS TO BE EXTRACTED (Column B)EXTRACTED DATA(Formula Cell - Need to extract the data based on the field tag mentioned in B Column) (Column C)
:PT17: XYZ123456
:PT04: 123456789
:PT09: ABCDEFGHIJ
:PT19:0550277877055027801105502780120550278013055027801
:PT21: 11-MAY-20
:PT28: Windows Explorer
:PT25: BE50826000543318
PT25BE50826000543318
:PT17: XYZ123456
:PT04: 123456789
:PT09: ABCDEFGHIJ
:PT19:0550277877055027801105502780120550278013055027801
:PT21: 11-MAY-20
:PT28: Windows Explorer
:PT25: BE50826000543318
PT28Windows Explorer
 
Upvote 0
How about
+Fluff v2.xlsm
ABC
2:PT17: XYZ123456 :PT04: 123456789 :PT09: ABCDEFGHIJ :PT19:0550277877055027801105502780120550278013055027801 :PT21: 11-MAY-20 :PT28: Windows Explorer :PT25: BE50826000543318PT25BE50826000543318
3:PT17: XYZ123456 :PT04: 123456789 :PT09: ABCDEFGHIJ :PT19:0550277877055027801105502780120550278013055027801 :PT21: 11-MAY-20 :PT28: Windows Explorer :PT25: BE50826000543318PT28Windows Explorer
Work
Cell Formulas
RangeFormula
C2:C3C2=TRIM(LEFT(SUBSTITUTE(REPLACE(A2,1,SEARCH(B2,A2)+LEN(B2),""),CHAR(10),REPT(" ",100)),100))
 
Upvote 0
As this is totally different from the original thread, please start a new thread of your own. Thanks
 
Upvote 0
How about
+Fluff v2.xlsm
ABC
2:PT17: XYZ123456 :PT04: 123456789 :PT09: ABCDEFGHIJ :PT19:0550277877055027801105502780120550278013055027801 :PT21: 11-MAY-20 :PT28: Windows Explorer :PT25: BE50826000543318PT25BE50826000543318
3:PT17: XYZ123456 :PT04: 123456789 :PT09: ABCDEFGHIJ :PT19:0550277877055027801105502780120550278013055027801 :PT21: 11-MAY-20 :PT28: Windows Explorer :PT25: BE50826000543318PT28Windows Explorer
Work
Cell Formulas
RangeFormula
C2:C3C2=TRIM(LEFT(SUBSTITUTE(REPLACE(A2,1,SEARCH(B2,A2)+LEN(B2),""),CHAR(10),REPT(" ",100)),100))
@ Fluff - Thanks for your response. I have case where there is a line break in the field were data needs to picked, and when I used this formula it's giving a Blank data. Could you please let me know the formula which needs to be updated?

exp:
:PT28:

Windows Explorer

:PT25:
BE50826000543318
 
Upvote 0
How about
Excel Formula:
=TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(REPLACE(A2,1,SEARCH(B2,A2)+LEN(B2),""),CHAR(10),""),":",REPT(" ",100)),100))
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top