CODEWITHSUNDEEP
excelexcel-formula
Ben A
Ben A

Reputation: 219

Excel function to cut data between to fixed character strings

I have the following text in an excel cell:

SampleID: S-2016-011451 SubmitterID: EIROSSME Sample Name: T1 BTMs - 6/26/16 10:00 PM Lot Nbr: ProductID:

I need to cut the data so that it reads as:

T1 BTMs 6/26/16 22:00

I can format the date using text($cell,"mm/dd/yy hh:mm") but I can't get the =mid(...) to truncate the data between "Name:" and " - ".

Upvotes: 0

Views: 41

Answers (3)

Nathan_Sav
Nathan_Sav

Reputation: 8531

Use SUBSTITUTE to change, so use SUBSTITUTE(a1,"Sample Name:","£££") and SUBSTITUTE(a1,"PM Lot Nbr:","$$$") together, then you'll get £££ T1 BTM......$$$ then you can find the instances of the £££ and $$$ and mid inbetween then, or RIGHT, then LEFT

This gives you the points to cut from and to. You can use the find of the values that we are also substituting, so find PM Lot Nbr etc.

SUBSTITUTE(SUBSTITUTE(J1,"Sample Name","@@@"),"PM Lot Nbr","|||")

Something similar to this (not complete, I shall finish and tidy)

=MID(SUBSTITUTE(SUBSTITUTE(J1,"Sample Name","@@@"),"PM Lot Nbr","|||"),LEN("@@@") + FIND("@@@",SUBSTITUTE(SUBSTITUTE(J1,"Sample Name","@@@"),"PM Lot Nbr","|||")),(FIND("|||",SUBSTITUTE(SUBSTITUTE(J1,"Sample Name","@@@"),"PM Lot Nbr","|||"))-FIND("@@@",SUBSTITUTE(SUBSTITUTE(J1,"Sample Name","@@@"),"PM Lot Nbr","|||"))-LEN("|||")))

Upvotes: 0

EBH
EBH

Reputation: 10450

Assuming you know what to expect after Sample Name: MID + SUB

  1. =MID(A1,SEARCH("Sample Name:",A1)+13,7)
  2. =MID(A1,SEARCH(A4,A1)+LEN(A4)+3,17)

And now you just have to convert the second cell to the date format you want (which you already know how) and concat them like A4&C4 (if C4 is the date after conversion).

Hope it helps ;)

Upvotes: 0

dot.Py
dot.Py

Reputation: 5157

enter image description here

1st: =mid(B2;63;26)
2nd: =mid(B5;1;8)
3rd: =mid(B5;11;18)
4th: =concatenate(B7;B8)

If you want to cut between Name: and -, just use:

=find("Name: ";B2)
=find(" -";B2)

and then:

=mid(B2, find("Name: ";B2)+5;find(" -";B2)-find("Name: ";B2)-5)

I.e.:

enter image description here

Upvotes: 1

Related Questions