1

I have an array of numbers in Excel beginning in B2 as follows:

CA.CAD.CP.0.0.0.0.1.CY
CA.CAD.CP.0.0.0.0.2.CY
CA.CAD.CP.0.0.0.1.0.CY
CA.CAD.CP.0.0.0.2.0.CY
CA.CAD.CP.0.0.3.0.0.CY
CA.CAD.CP.0.0.0.6.0.CY
CA.CAD.OIS.0.0.0.1.0.CY
CA.CAD.OIS.0.0.0.2.0.CY
CA.CAD.OIS.0.0.0.3.0.CY
CA.CAD.OIS.0.0.6.0.0.CY
CA.CAD.OIS.0.0.0.9.0.CY
CA.CAD.OIS.1.0.0.0.0.CY
CA.CAD.ONT.0.0.0.1.0.CY
CA.CAD.ONT.0.0.0.2.0.CY
CA.CAD.ONT.0.0.0.3.0.CY
CA.CAD.ONT.0.0.6.0.0.CY
CA.CAD.ONT.1.0.0.0.0.CY

for several thousand rows. All of them follow this exact format. The numbers represent a date format; D.W.F.M.Y. So 0.0.0.5.0 means 5 months, for example.

I want to find all instances where the date value is "F", meaning all instances of "xx.xxx.xxx.0.0.x.0.0".

What is the best way to do this? I have tried using the FIND function but I think there might be a better way to search for this string.

Improve this question
6
  • Why does the find not work for you? Commented Jun 21, 2016 at 13:59
  • Could you also provide some data that had the "F", none of the above appear to have it. Commented Jun 21, 2016 at 14:00
  • i am not looking for one specific string (for instance 0.0.1, 0.0.2, 0.0.3 etc are all admissible values) so FIND did not work; is there a way to fix it to accommodate that? Commented Jun 21, 2016 at 14:01
  • edited it to add data with F Commented Jun 21, 2016 at 14:02
  • If your dates are always formatted like the above, you could pull the date out with Mid(), no? Commented Jun 21, 2016 at 14:05

3 Answers 3

Reset to default
4

This will return True/False based on whether the middle, or "F" position is anything but 0 or not:

=--MID(SUBSTITUTE(B2,".",REPT(" ",99)),5*99,99)<>0

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

what does the "--" before MID in the formula do?
It changes the result from a string to a number. @beeba
I've seen this sort of method before and it clearly works well but haven't really understood it. I think I've got it now - if you choose a number of spaces much bigger than the string itself and do the substitution, each block of (say) 99 characters contains a few spaces, a string or number, then a lot of spaces and on the next step you just drop the spaces using TRIM or converting to a number. Sorry, just thinking aloud.
@TomSharpe yes when returning a string it would need to be wrapped with TRIM to remove the extra spaces or it would have a lot. It gives us a large target to use as a split. With it being numbers using mathematical function causes excel to automatically remove the space as it tries to make the string a number.
3

With data starting in A2, in B2 enter:

=TRIM(MID(SUBSTITUTE($A2,".",REPT(" ",999)),COLUMNS($A:A)*999-998,999))

and copy across and then down:

enter image description here

Then set an AutoFilter on column G to display non-zero values.

Improve this answer

Comments

1

Have you thought to use Word's Find feature? I understand it's in excel - but copy and paste data into Word - it's Find capabilities allow you to search for variables even formatting and special characters including tabs, and punctuation - you can use the Find/Replace feature to have it perform some special maneuvers to mark your text before simply copy/paste special back into excel when finished with Word's special unique features - it's find/replace capabilities are stronger than any other Office program

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.