Extract text and date from cell value

[sharky12345]

I need to extract certain text and a date from a filename which is stored in a cell.

The value is like this;

Raw Data (For Import only) - Headoffice 01_04_2018 (Late).xlsx

The text I need will be the part that is shown as 'Headoffice' above, (this can vary in text length.

The date I need is the part shown as '01_04_2018' above and I will need the result formatted as '01/04/2018'.

Note - I need 2 separate formulas for this, one for the text and the other for the date - the results can go anywhere on the sheet so perhaps column B same row?

 

[SteveO59L]

so, extract between the "-" and the "(" characters?

 

[sharky12345]

Hi - Yes!

 

[Scott T]

If the text will not have spaces this should work

Excel 2010
	A	B	C
2	Raw Data (For Import only) - Headoffice 01_04_2018 (Late).xlsx	Headoffice	1/4/2018
3	test Raw Data (For Import only) - Headtestoffice 01_24_2018 (Late).xlsx	Headtestoffice	1/24/2018
Sheet1

Cell Formulas
Range	Formula
B2	=TRIM(MID(A2,SEARCH("-",A2)+1,SEARCH(" ",MID(A2,SEARCH("- ",A2)+2,9999))))
B3	=TRIM(MID(A3,SEARCH("-",A3)+1,SEARCH(" ",MID(A3,SEARCH("- ",A3)+2,9999))))
C2	=DATE(MID(A2,SEARCH("_",A2)+4,4),MID(A2,SEARCH("_",A2)-2,2),MID(A2,SEARCH("_",A2)+1,2))
C3	=DATE(MID(A3,SEARCH("_",A3)+4,4),MID(A3,SEARCH("_",A3)-2,2),MID(A3,SEARCH("_",A3)+1,2))

 

[Gerald Higgins]

We can potentially use the underscore characters ( _ ) to identify the date element.
Could there be underscore characters in any other positions in the text string ?

We can also potentially use the hyphen character to identify the text element.

This will extract the date
=DATE(MID(A1,FIND("_",A1,FIND("_",A1,1)+1)+1,4)+0,MID(A1,FIND("_",A1)+1,2)+0,MID(A1,FIND("_",A1)-2,2)+0)

and this will extract the word "Headoffice"
=MID(A1,FIND("-",A1)+2,FIND("_",A1)-FIND("-",A1)-5)

BUT, this looks like one of those situations where you might have lots of different types of input, and want slightly different results.
If that is the case, we may need to adapt these solutions - in which case please post more samples showing the variety of your input data.

 

[sharky12345]

Guys - very grateful for all of your suggestions, they all do what I need so thank you!

 

[skorpionkz]

	B
Raw Data (For Import only) - Headoffice 01_04_2018 (Late).xlsx
Headoffice
01_04_2018

<colgroup><col style="width: 25pxpx" https:="" www.mrexcel.com="" forum="" usertag.php?do="list&action=hash&hash=DAE7F5"" target="_blank"></colgroup><colgroup><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]14[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: center"]17[/TD]

</tbody>

​

[TABLE="width: 85%"]
<tbody>[TR]
[TD][TABLE="width: 100%"]
<thead>[TR="bgcolor: &lt"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: &lt"]B16[/TH]
[TD="align: left"]=LEFT(TRIM(MID(B14,FIND("-",B14)+1,FIND("(",B14,FIND("-",B14)+1)-FIND("-",B14)-1)),FIND(" ",TRIM(MID(B14,FIND("-",B14)+1,FIND("(",B14,FIND("-",B14)+1)-FIND("-",B14)-1)))-1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: &lt"]B17[/TH]
[TD="align: left"]=RIGHT(TRIM(MID(B14,FIND("-",B14)+1,FIND("(",B14,FIND("-",B14)+1)-FIND("-",B14)-1)),FIND(" ",TRIM(MID(B14,FIND("-",B14)+1,FIND("(",B14,FIND("-",B14)+1)-FIND("-",B14)-1)))-1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[sharky12345]

Guys - one further request if I may?

I need to get the value between the 2 commas, in the example shown about it would be 'Late' as follows;

(Late)

A doddle for all of you I'm sure!

 

[Scott T]

try

=MID(A2,SEARCH("_",A2)+10,SEARCH(")",MID(A2,SEARCH("_",A2)+10,9999))-1)

 

[sharky12345]

Perfect - thanks for all of your help Scott T!