Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default Number of Occurrences of a String Value within a String

I am analysing some string data mostly for numeric values, but want to
remove those values that look like dates (e.g. 12/12/2008). Part of
the analysis means that I must count the number of occurrences on the
value "/" within the string.

Does anyone know of a function that would give me the count of the
number of instances of "/" or any other string portion?
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,501
Default Number of Occurrences of a String Value within a String

Try

=LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))

Mike

"Stephen Allen" wrote:

I am analysing some string data mostly for numeric values, but want to
remove those values that look like dates (e.g. 12/12/2008). Part of
the analysis means that I must count the number of occurrences on the
value "/" within the string.

Does anyone know of a function that would give me the count of the
number of instances of "/" or any other string portion?

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,058
Default Number of Occurrences of a String Value within a String

With the text value in A1, use:

=LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))

this will give the count of slashes in the cell.

NOTE the data must be text and not a Date nor formulaic divide
--
Gary''s Student - gsnu200816


"Stephen Allen" wrote:

I am analysing some string data mostly for numeric values, but want to
remove those values that look like dates (e.g. 12/12/2008). Part of
the analysis means that I must count the number of occurrences on the
value "/" within the string.

Does anyone know of a function that would give me the count of the
number of instances of "/" or any other string portion?

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,651
Default Number of Occurrences of a String Value within a String

=(LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))/LEN("/")

Obviously the division by LEN("/") is unnecessary if the string portion you
are looking for is a single character, but it allows for the more general
case if you were looking for a longer string portion.
--
David Biddulph

"Stephen Allen" wrote in message
...
I am analysing some string data mostly for numeric values, but want to
remove those values that look like dates (e.g. 12/12/2008). Part of
the analysis means that I must count the number of occurrences on the
value "/" within the string.

Does anyone know of a function that would give me the count of the
number of instances of "/" or any other string portion?



  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default Number of Occurrences of a String Value within a String

Thank you all. It works a treat.

On Nov 26, 1:30*pm, Stephen Allen wrote:
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Change 3 letter text string to a number string Pete Excel Discussion (Misc queries) 3 December 31st 07 08:47 PM
counting the number of instances of a string within another string Keith R Excel Worksheet Functions 3 March 5th 07 07:54 PM
Splitting a text string into string and number mcambrose Excel Discussion (Misc queries) 4 February 21st 06 04:47 PM
to search for a string and affect data if it finds the string? Shwaman Excel Worksheet Functions 1 January 11th 06 01:56 AM
how to count the nr of occurrences of a text string in a cell rang eagerbuyer Excel Worksheet Functions 1 November 4th 04 01:27 PM


All times are GMT +1. The time now is 02:31 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"