Extract some data from a long string in a cell

[mmopulencia]

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 data

Full Name

QID

Password

Engagement date

Kind regards,
Mike

 

[DanteAmor]

A little correction:

=TRIM(MID(SUBSTITUTE($A2,"[CR][LF][CR][LF]",REPT(" ",999)),(COLUMNS($B1:B1)*2+6)*999-997,999))

 

[Fluff]

Another option

+Fluff 1.xlsm
	A	B	C	D	E
1		Full Name	QID	pasword	engagementdate
2	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]	Gordon Xavier	Q20123123111	sucksometite11	01/10/2021
Master

Cell Formulas
Range	Formula
B2:E2	B2	=FILTERXML("<k><m>"&SUBSTITUTE($A2,"[CR][LF][CR][LF]","</m><m>")&"</m></k>","//m[.='"&B1&"']/following-sibling::m[1]")

Note the headers must be exactly the same as the text, including case.

 

[Peter_SSs]

or a macro

An option along those lines may be a user-defined function like below. If you need instructions on how to implement it post back.

Notes

1. At the moment this returns the date as a string. If you need it as a date (number) post back.
2. At the moment the 'case' of the headers needs to match what is in the text. If that can vary, an easy adjustment can be made.
3. In your example, the items being extracted from the text are in the same order as the headings. Is that always the case?

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Function GetPart(sData As String, sPart As String) As String
  With CreateObject("VBScript.RegExp")
    .Pattern = "(" & sPart & "[CRLF\[\]]+)(.+?)(?=\[)"
    If .Test(sData) Then GetPart = .Execute(sData)(0).SubMatches(1)
  End With
End Function

mmopulencia.xlsm
	A	B	C	D	E
1	Data	Full Name	QID	pasword	engagementdate
2	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]	Gordon Xavier	Q20123123111	sucksometite11	1-Oct-21
Sheet1

Cell Formulas
Range	Formula
B2:E2	B2	=GetPart($A2,B$1)

 

[Peter_SSs]

Here is another worksheet formula approach that, like Fluff's, would also cope if there is not a direct correlation between the header order and the item order in the raw data.

mmopulencia.xlsm
	A	B	C	D	E
1	Data	QID	Full Name	engagementdate	pasword
2	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]	Q20123123111	Gordon Xavier	1-Oct-21	sucksometite11
Sheet1 (2)

Cell Formulas
Range	Formula
B2:E2	B2	=REPLACE(LEFT($A2,FIND("[",$A2,FIND(B$1,$A2)+LEN(B$1)+13)-1),1,FIND("]",$A2,FIND(B$1,$A2)+LEN(B$1)+13),"")