Extract info from a string? 1

[supportsvc]

There's a field with information in it that's combined rather than separated out.

And unfortunately it varies
Example of the variations

12/1/2017 P11345-PP 12/6/2017
12/15/2017 B124536-CM 12/31/2017
2/28/2018 A919777-IN 3/30/2018
3/7/2018 A959264-IN 4/6/2018
10/15/2018 C234364-DM 11/1/2018
11/1/2018 P23465-PP
{/code}

1st part is the InvoiceDate
2nd part is the InvoiceNo (without the -IN, etc ... always 7 without the -IN except when it starts with P or ends with PP, then it varies but max is 7)
3rd part is the InvoiceType (just the 2 letters, that's always consistent with 2 letters)
4th part is the InvoiceDueDate (sometimes there is no InvoiceDueDate (last example above)

The Text to Columns only works on some of them and have to do it for each record that's different. 
So looking for a formula instead for a much faster extraction.

 

[SkipVought]

Hi,

SELECT ALL your data

Data > Text to columns...

DELIMITED on [highlight #FCE94F]BOTH[/highlight]:
[highlight #FCE94F]SPACE[/highlight] & [highlight #FCE94F]-[/highlight]

my results
[pre]
12/1/2017 P11345 PP 12/6/2017
12/15/2017 B124536 CM 12/31/2017
2/28/2018 A919777 IN 3/30/2018
3/7/2018 A959264 IN 4/6/2018
10/15/2018 C234364 DM 11/1/2018
11/1/2018 P23465 PP
[/pre]



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[supportsvc]

Thank you!

Though still have to do it in sections just do to how the data was extracted from a PDF and the alignment isn't consistent.

 

[SkipVought]

If you were to add the headings as below, then your table would be complete...
[tt]
InvoiceDate InvoiceNo-InvoiceType InvoiceDueDate
12/1/2017 P11345-PP 12/6/2017
12/15/2017 B124536-CM 12/31/2017
2/28/2018 A919777-IN 3/30/2018
3/7/2018 A959264-IN 4/6/2018
10/15/2018 C234364-DM 11/1/2018
11/1/2018 P23465-PP
[/tt]

Results...
[pre]
InvoiceDate InvoiceNo InvoiceType InvoiceDueDate
12/1/2017 P11345 PP 12/6/2017
12/15/2017 B124536 CM 12/31/2017
2/28/2018 A919777 IN 3/30/2018
3/7/2018 A959264 IN 4/6/2018
10/15/2018 C234364 DM 11/1/2018
11/1/2018 P23465 PP
[/pre]


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]