0

I have been tasked with extracting certain data from a report. We have added in something into our script software that tells us how long a call advisor has spent in a script. This is almost always right in the middle of a string, strings vary in length.

I have added in underscores either side of the text I need to extract to make it easier, however ideally I would like to only use one underscore at the end to separate it. Below are some examples of how the strings appear:

ZZ TEST SCRIPT FOR TESTING ONLY: Inventory test; Yes; Time spent in script:11

Hi, we have received a call from Ms Customer regarding their issue: ..._Time spent in script:123_Q1 = Who do you want to task?: A1 = 'Other'....

What I am after is a way to extract just the numerical figure after: "Time spent in script:" These figures can be between 1-4 characters in length. Potentially 5 but unlikely. The way I have tried to extract the characters to the left of the second underscore (I can remove the first underscore so that all future reports will not have this), however this is on the assumption that there are no other underscores in the string. We often have email addresses in there and a number of those have underscores.

I know there are likely a number of ways to do this, been stuck on this for hours, as I come from a SQL background, so Excel is not my forte.

Craig Jones
  • 41
  • 7
  • It's likely that you will want to use regular expressions: http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops – maxhob17 Nov 10 '16 at 15:29
  • Is the number *always* preceded by "Time spent in script:"? – tigeravatar Nov 10 '16 at 15:37
  • Hi tigeravatar, yes it is, it will always be in that format. Maxhob17, I am currently reading through this to learn more about it, never used macros in excel before so it's slow going! – Craig Jones Nov 10 '16 at 15:57

1 Answers1

0

Although the following describes a more roundabout way of getting the answer, it does avoid the need for macros.

Assuming your string is in A1:

B1 (starting character of target string): =FIND("Time spent in script:",A1)

C1 (length of target string): =LEN("Time spent in script:") = 21

D1 (location of first "_" after target string): =FIND("_",A1,B1)

E1 (result): =INT(MID(A1,B1+C1,D1-B1-C1))

aakash
  • 143
  • 6