Extract some data from a long string in a cell

M

mmopulencia

New Member
Joined
Feb 19, 2013
Messages
30
Hello Team,

looking into getting a formula by cell or a macro that does the following . The below cell is a sample on what a cell may contain (the numbers of character are not constant)

whats constant is it has a table, which ive managed to extract into containing somewhat a constant marker.([CR][LF][CR][LF])

Bla blab la[CR][LF][CR][LF] [CR][LF][CR][LF]Adf,.sdnf,sndf.,ns.,fns,d.nf,.dsnf,.sndfbsdjhfgsdjkfgjkdsfasdfjksghfjsgadjhfsadf[CR][LF][CR][LF]Sdaflkjhsdaklfhlksdahflkjhsdklfhskdaf[CR][LF][CR][LF]Adkjflkashdflkjhsadlfkhdsf[CR][LF][CR][LF] [CR][LF][CR][LF]Full Name[CR][LF][CR][LF]Gordon Xavier[CR][LF][CR][LF]QID[CR][LF][CR][LF]Q20123123111[CR][LF][CR][LF]pasword[CR][LF][CR][LF]sucksometite11[CR][LF][CR][LF]engagementdate[CR][LF][CR][LF]1-Oct-21[CR][LF][CR][LF] [CR][LF][CR][LF] [CR][LF][CR][LF]



from the above cell , i need to extract the full name , QID , Password, Engagement date , on the right side of this cell

Raw dataFull NameQIDPasswordEngagement date

Kind regards,
Mike
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
Hello Mike

Try this:
Excel Formula: 
=TRANSPOSE(INDEX(FILTERXML("<y><z>"&SUBSTITUTE(A1,"[CR][LF][CR][LF]","</z><z>")&"</z></y>","//z[position() mod 2=0]"),{4;5;6;7}))
 
Upvote 0
You must have Office 365 because the formula spills.

Book1
ABCD
5Full NameQIDpaswordengagementdate
6Gordon XavierQ20123123111sucksometite111-Oct-21
Tabelle2
Cell Formulas
RangeFormula
A6A6=TRANSPOSE(INDEX(FILTERXML("<y><z>"&SUBSTITUTE(A1,"[CR][LF][CR][LF]","</z><z>")&"</z></y>","//z[position() mod 2=0]"),{4;5;6;7}))
 
Upvote 0
Here are other formulas to consider.

Dante Amor
ABCDE
1RAWFull NameQIDPasswordEngagement date
2Bla blab la[CR][LF][CR][LF] [CR][LF][CR][LF]Adf,.sdnf,sndf.,ns.,fns,d.nf,.dsnf,.sndfbsdjhfgsdjkfgjkdsfasdfjksghfjsgadjhfsadf[CR][LF][CR][LF]Sdaflkjhsdaklfhlksdahflkjhsdklfhskdaf[CR][LF][CR][LF]Adkjflkashdflkjhsadlfkhdsf[CR][LF][CR][LF] [CR][LF][CR][LF]Full Name[CR][LF][CR][LF]Gordon Xavier[CR][LF][CR][LF]QID[CR][LF][CR][LF]Q20123123111[CR][LF][CR][LF]pasword[CR][LF][CR][LF]sucksometite11[CR][LF][CR][LF]engagementdate[CR][LF][CR][LF]1-Oct-21[CR][LF][CR][LF] [CR][LF][CR][LF] [CR][LF][CR][LF] Gordon XavierQ20123123111sucksometite111-Oct-21
Hoja4
Cell Formulas
RangeFormula
B2B2=TRIM(MID(SUBSTITUTE($A2,"[CR][LF][CR][LF]",REPT(" ",255)),8*255-253,255))
C2C2=TRIM(MID(SUBSTITUTE($A2,"[CR][LF][CR][LF]",REPT(" ",255)),10*255-253,255))
D2D2=TRIM(MID(SUBSTITUTE($A2,"[CR][LF][CR][LF]",REPT(" ",255)),12*255-253,255))
E2E2=TRIM(MID(SUBSTITUTE($A2,"[CR][LF][CR][LF]",REPT(" ",255)),14*255-253,255))
 
Upvote 0
Hello Shift,
got it to work, only problem now is sometimes the data before fullname, and after Engagement date tend to change or vary, only thing constant is is that the values in the middle . which has the fullname to engagement, i tried it with a raw that has more data before and after the constant part and that kicks a #value error.

regards,
Mike
 
Upvote 0
DanteAmor said:
Here are other formulas to consider.

Dante Amor
ABCDE
1RAWFull NameQIDPasswordEngagement date
2Bla blab la[CR][LF][CR][LF] [CR][LF][CR][LF]Adf,.sdnf,sndf.,ns.,fns,d.nf,.dsnf,.sndfbsdjhfgsdjkfgjkdsfasdfjksghfjsgadjhfsadf[CR][LF][CR][LF]Sdaflkjhsdaklfhlksdahflkjhsdklfhskdaf[CR][LF][CR][LF]Adkjflkashdflkjhsadlfkhdsf[CR][LF][CR][LF] [CR][LF][CR][LF]Full Name[CR][LF][CR][LF]Gordon Xavier[CR][LF][CR][LF]QID[CR][LF][CR][LF]Q20123123111[CR][LF][CR][LF]pasword[CR][LF][CR][LF]sucksometite11[CR][LF][CR][LF]engagementdate[CR][LF][CR][LF]1-Oct-21[CR][LF][CR][LF] [CR][LF][CR][LF] [CR][LF][CR][LF] Gordon XavierQ20123123111sucksometite111-Oct-21
Hoja4
Cell Formulas
RangeFormula
B2B2=TRIM(MID(SUBSTITUTE($A2,"[CR][LF][CR][LF]",REPT(" ",255)),8*255-253,255))
C2C2=TRIM(MID(SUBSTITUTE($A2,"[CR][LF][CR][LF]",REPT(" ",255)),10*255-253,255))
D2D2=TRIM(MID(SUBSTITUTE($A2,"[CR][LF][CR][LF]",REPT(" ",255)),12*255-253,255))
E2E2=TRIM(MID(SUBSTITUTE($A2,"[CR][LF][CR][LF]",REPT(" ",255)),14*255-253,255))
Click to expand...
Hello Dante,

If the text prior to full name is longer (since that is not constant, if bugs out (i guess adjusts the count) and just displays the word "full name". post the data tho even if theres a lot data on the opposite end it seems it still works.

Regards,

Mike
 
Upvote 0
The formula relies that full name, QID, password and engagementdate are always in the 8th, 10th, 12th and 14th position separated by [CR][LF][CR][LF].

Book1
ABCD
5Full NameQIDpaswordengagementdate
6Gordon XavierQ20123123111sucksometite111-Oct-21
7Gordon XavierQ20123123111sucksometite111-Oct-21
Tabelle2
Cell Formulas
RangeFormula
A6A6=TRANSPOSE(INDEX(FILTERXML("<y><z>"&SUBSTITUTE(A1,"[CR][LF][CR][LF]","</z><z>")&"</z></y>","//z[position() mod 2=0]"),{4;5;6;7}))
A7A7=TRANSPOSE(INDEX(FILTERXML("<y><z>"&SUBSTITUTE(A1,"[CR][LF][CR][LF]","</z><z>")&"</z></y>","//z"),{8;10;12;14}))
 
Upvote 0
mmopulencia said:
If the text prior to full name is longer
Click to expand...
Try this

It is a single formula, copy in B2 and drag to the right

Dante Amor
ABCDE
1RAWFull NameQIDPasswordDate
2Bla blab la[CR][LF][CR][LF] [CR][LF][CR][LF]Adf,.sdnf,sndf.,ns.,fns,d.nf,.dsnf,.sndfbsdjhfgsdjkfgjkdsfasdfjksghfjsgadjhfsadf[CR][LF][CR][LF]Sdaflkjhsdaklfhlksdahflkjhsdklfhskdaf[CR][LF][CR][LF]Adkjflkashdflkjhsadlfkhdsf[CR][LF][CR][LF] [CR][LF][CR][LF]Full Name[CR][LF][CR][LF]Gordon Xavier[CR][LF][CR][LF]QID[CR][LF][CR][LF]Q20123123111[CR][LF][CR][LF]pasword[CR][LF][CR][LF]sucksometite11[CR][LF][CR][LF]engagementdate[CR][LF][CR][LF]1-Oct-21[CR][LF][CR][LF] [CR][LF][CR][LF] [CR][LF][CR][LF] Gordon XavierQ20123123111sucksometite111-Oct-21
Hoja4
Cell Formulas
RangeFormula
B2:E2B2=TRIM(MID(SUBSTITUTE($A2,"[CR][LF][CR][LF]",REPT(" ",999)),(COLUMNS($B$13:B13)*2+6)*999-997,999))
 
Upvote 0
Got another one.

Book1
ABCD
5Full NameQIDpaswordengagementdate
6Gordon XavierQ20123123111sucksometite111-Oct-21
7Gordon XavierQ20123123111sucksometite111-Oct-21
8Gordon XavierQ20123123111sucksometite111-Oct-21
Tabelle2
Cell Formulas
RangeFormula
A6A6=TRANSPOSE(INDEX(FILTERXML("<y><z>"&SUBSTITUTE(A1,"[CR][LF][CR][LF]","</z><z>")&"</z></y>","//z[position() mod 2=0]"),{4;5;6;7}))
A7A7=TRANSPOSE(INDEX(FILTERXML("<y><z>"&SUBSTITUTE(A1,"[CR][LF][CR][LF]","</z><z>")&"</z></y>","//z"),{8;10;12;14}))
A8A8=LET( a,FILTERXML("<y><z>"&SUBSTITUTE(A1,"[CR][LF][CR][LF]","</z><z>")&"</z></y>","//z[position()>0 and position()<17]"), b,FILTERXML("<y><z>"&SUBSTITUTE(A1,"[CR][LF][CR][LF]","</z><z>")&"</z></y>","//z[position()>1 and position()<18]"), XLOOKUP({"Full Name","QID","pasword","engagementdate"},a,b))
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,215,204
Messages
6,123,630
Members
449,109
Latest member
Sebas8956

Share this page

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