Extract and be able to sort from a text string

[-emma-]

Hi everyone,

Silly work and out dated systems have been causing me problems all day! We have a system that information is extracted from and the results are as below...

JohnSmith	Feb 22, '20 23:09	France	Manhattan	Offline	Verified

MR_Armstrong	Feb 09, '20 20:27	England	Brooklyn	Offline	Verified

What I need to be able to do is put this in some sort of manageable table. So I can filter, sort, extract etc. I've tried and managed to get some parts working, all be it messy, but the date is the thing that is stumping me the most. I can only ever get it to sort in Alpha order, no matter how I seem to format the cell.

Ideally, I would like to dump all the information in Tab 1 and Tab 2 spits out a pretty result.
Example of Tab 1 would be A2-A999 a dump of info as above
Example of Tab 2 would have headings of Name (A1), Date (B1), Location (C1) and Final Destination (D1) and the results in rows 2-999 extracted from Tab 1 data. (The rest of the information is not needed)

Please remember that I need to be able to sort on all things, especially Date.

Any help on this would be great.

Thanks

 

[kweaver]

Not sure this is the most efficient, but I think it does what you want:

Book1
	A	B	C	D	E
1		Name	Data	Location	Destination
2	JohnSmith;Feb 22, 2020 23:09;France;Manhattan;Offline;Verified	JohnSmith	Feb 22 2020 23:09	France	Manhattan
3	MR_Armstrong;Feb 09, 2020 20:27;England;Brooklyn;Offline;Verified	MR_Armstrong	Feb 09 2020 20:27	England	Brooklyn
Sheet2

Cell Formulas
Range	Formula
A2:A3	A2	=SUBSTITUTE(SUBSTITUTE(Sheet1!A2,"&#9",""),"'","20")
B2:B3	B2	=LEFT(A2,FIND(";",A2)-1)
C2:C3	C2	=--MID(A2,FIND(";",A2)+1,(FIND(";",A2,1+FIND(";",A2)))-FIND(";",A2)-1)
D2:D3	D2	=MID(A2,FIND(CHAR(160),SUBSTITUTE(A2,";",CHAR(160),2))+1,FIND(CHAR(160),SUBSTITUTE(A2,";",CHAR(160),3))-FIND(CHAR(160),SUBSTITUTE(A2,";",CHAR(160),2))-1)
E2:E3	E2	=MID(A2,FIND(CHAR(160),SUBSTITUTE(A2,";",CHAR(160),3))+1,FIND(CHAR(160),SUBSTITUTE(A2,";",CHAR(160),4))-FIND(CHAR(160),SUBSTITUTE(A2,";",CHAR(160),3))-1)

 

[DanteAmor]

Here another option for you to consider.
In columns B to E the same formula and in column H the formula for the date.

Dante Amor
	A	B	C	D	E	F
1	TEXT	NAME	DATE AND TIME	LOCATION	DESTINATION	DATE
2	JohnSmith	Feb 22, '20 23:09	France	Manhattan	Offline	Verified	JohnSmith	Feb 22, '20 23:09	France	Manhattan	22/02/2020
3	MR_Armstrong	Feb 09, '20 20:27	England	Brooklyn	Offline	Verified	MR_Armstrong	Feb 09, '20 20:27	England	Brooklyn	09/02/2020
s3

Cell Formulas
Range	Formula
B2:E3	B2	=TRIM(MID(SUBSTITUTE($A2,"	",REPT(" ",99)),((COLUMNS($B2:B2)-1)*99)+1,99))
F2:F3	F2	=(MID(C2,5,2)&"/"&LEFT(C2,3)&"/20"&MID(C2,10,2))+0

 

[-emma-]

This works great, apart from Column C. I get #VALUE as a response

 

[-emma-]

Not sure this is the most efficient, but I think it does what you want:

Book1
	A	B	C	D	E
1		Name	Data	Location	Destination
2	JohnSmith;Feb 22, 2020 23:09;France;Manhattan;Offline;Verified	JohnSmith	Feb 22 2020 23:09	France	Manhattan
3	MR_Armstrong;Feb 09, 2020 20:27;England;Brooklyn;Offline;Verified	MR_Armstrong	Feb 09 2020 20:27	England	Brooklyn
Sheet2

Cell Formulas
Range	Formula
A2:A3	A2	=SUBSTITUTE(SUBSTITUTE(Sheet1!A2,"&#9",""),"'","20")
B2:B3	B2	=LEFT(A2,FIND(";",A2)-1)
C2:C3	C2	=--MID(A2,FIND(";",A2)+1,(FIND(";",A2,1+FIND(";",A2)))-FIND(";",A2)-1)
D2:D3	D2	=MID(A2,FIND(CHAR(160),SUBSTITUTE(A2,";",CHAR(160),2))+1,FIND(CHAR(160),SUBSTITUTE(A2,";",CHAR(160),3))-FIND(CHAR(160),SUBSTITUTE(A2,";",CHAR(160),2))-1)
E2:E3	E2	=MID(A2,FIND(CHAR(160),SUBSTITUTE(A2,";",CHAR(160),3))+1,FIND(CHAR(160),SUBSTITUTE(A2,";",CHAR(160),4))-FIND(CHAR(160),SUBSTITUTE(A2,";",CHAR(160),3))-1)

This works great, apart from Column C. I get #VALUE as a response

 

[-emma-]

Here another option for you to consider.
In columns B to E the same formula and in column H the formula for the date.

Dante Amor
	A	B	C	D	E	F
1	TEXT	NAME	DATE AND TIME	LOCATION	DESTINATION	DATE
2	JohnSmith	Feb 22, '20 23:09	France	Manhattan	Offline	Verified	JohnSmith	Feb 22, '20 23:09	France	Manhattan	22/02/2020
3	MR_Armstrong	Feb 09, '20 20:27	England	Brooklyn	Offline	Verified	MR_Armstrong	Feb 09, '20 20:27	England	Brooklyn	09/02/2020
s3

Cell Formulas
Range	Formula
B2:E3	B2	=TRIM(MID(SUBSTITUTE($A2,"	",REPT(" ",99)),((COLUMNS($B2:B2)-1)*99)+1,99))
F2:F3	F2	=(MID(C2,5,2)&"/"&LEFT(C2,3)&"/20"&MID(C2,10,2))+0

This is ideal! But I need the Date section to include time. Sorry, I know I'm being a pain! Is it a quick fix? Or am I formatting the cell incorrectly?

 

[kweaver]

I formatted my column C and you can use Dante's with the right formatting as well.

 

[DanteAmor]

But I need the Date section to include time

Change the formula in cell F2 to this and format cell to : dd/mm/yyyy hh:mm

=(MID(C2,5,2)&"/"&LEFT(C2,3)&"/20"&MID(C2,10,2) & " " & RIGHT(C2,5))+0

 

[Peter_SSs]

Another slight variation to consider.
Each formula copied down.
Format the Date/Time column with whatever format you want.

20 02 28.xlsm
	A	B	C	D	E
1	Data	Name	Date/Time	Location	Destination
2	JohnSmith	Feb 22, '20 23:09	France	Manhattan	Offline	Verified	JohnSmith	22-Feb-20 23:09	France	Manhattan
3	MR_Armstrong	Feb 09, '20 20:27	England	Brooklyn	Offline	Verified	MR_Armstrong	09-Feb-20 20:27	England	Brooklyn
Split Text

Cell Formulas
Range	Formula
B2:B3	B2	=LEFT(A2,FIND("&",A2)-1)
C2:C3	C2	=DATEVALUE(SUBSTITUTE(MID(A2,FIND(" ",A2),8),", '",MID(A2,FIND(";",A2)+1,3)))+MID(A2,FIND("'",A2)+4,5)
D2:D3	D2	=TRIM(MID(SUBSTITUTE(A2,"	",REPT(" ",100)),200,100))
E2:E3	E2	=TRIM(MID(SUBSTITUTE(A2,"	",REPT(" ",100)),300,100))

 

[sandy666]

another option with Power Query

Name	DateTime	Loc	Dest
JohnSmith	Feb 22, '20 23:09	France	Manhattan
MR_Armstrong	Feb 09, '20 20:27	England	Brooklyn

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    BF = Table.AddColumn(Source, "Name", each Text.BeforeDelimiter([TEXT], " "), type text),
    BT1 = Table.AddColumn(BF, "DateTime", each Text.BetweenDelimiters([TEXT], " ", " ", 0, 3), type text),
    BT2 = Table.AddColumn(BT1, "Loc", each Text.BetweenDelimiters([TEXT], " ", " ", 4, 0), type text),
    BT3 = Table.AddColumn(BT2, "Dest", each Text.BetweenDelimiters([TEXT], " ", " ", 5, 0), type text),
    TSC = Table.SelectColumns(BT3,{"Name", "DateTime", "Loc", "Dest"})
in
    TSC