Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 8th 18, 07:58 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2007
Posts: 409
Default Numbers stuck in strings. Does this number belong?

Hello,

I have a string that resembles 1,2,5-7,13. I have another cell that has a number in it. I want to know if that number is in the first number.

So if I have:
1 - I want to return true
2 - I want to return true
6 - I want to return true (6 is not one of the numbers in the string but it is between 5 and 7.
10 - I want to return false


Kind Regards,
Jay

  #2   Report Post  
Old May 8th 18, 08:49 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,619
Default Numbers stuck in strings. Does this number belong?

Hi,

Am Tue, 8 May 2018 11:58:11 -0700 (PDT) schrieb jlclyde:

I have a string that resembles 1,2,5-7,13. I have another cell that has a number in it. I want to know if that number is in the first number.

So if I have:
1 - I want to return true
2 - I want to return true
6 - I want to return true (6 is not one of the numbers in the string but it is between 5 and 7.
10 - I want to return false


your string in A1, the number you are searching for in B1.
Then call the function in the sheet with
=myMatch(A1,B1)

Function myMatch(myRng As Range, SearchRng As Range) As Boolean
Dim vardata As Variant, varTmp As Variant
Dim i As Integer

vardata = Split(myRng, ",")
For i = LBound(vardata) To UBound(vardata)
If Not IsNumeric(vardata(i)) Then
varTmp = Split(vardata(i), "-")
If SearchRng = CInt(varTmp(0)) And SearchRng <= CInt(varTmp(1))
Then
myMatch = True
Exit For
End If
ElseIf IsNumeric(vardata(i)) And CInt(vardata(i)) = SearchRng Then
myMatch = True
Exit For
End If
Next
End Function


Regards
Claus B.
--
Windows10
Office 2016
  #3   Report Post  
Old May 10th 18, 01:55 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2007
Posts: 228
Default Numbers stuck in strings. Does this number belong?

On Tuesday, May 8, 2018 at 2:58:14 PM UTC-4, jlclyde wrote:
Hello,

I have a string that resembles 1,2,5-7,13. I have another cell that has a number in it. I want to know if that number is in the first number.

So if I have:
1 - I want to return true
2 - I want to return true
6 - I want to return true (6 is not one of the numbers in the string but it is between 5 and 7.
10 - I want to return false


Kind Regards,
Jay


Cross-post: https://groups.google.com/forum/#!to...sc/DTw02f3uFW4


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
Converting strings to numbers Big Bitty Excel Programming 2 April 29th 10 03:31 PM
Combine strings and numbers Alain Dekker Excel Discussion (Misc queries) 6 February 21st 10 08:51 PM
Strings to Numbers open a adobe file from a command button Excel Programming 1 April 9th 07 05:42 AM
How to find number of pairs of strings from list of strings? greg_overholt Excel Worksheet Functions 5 January 27th 06 10:42 PM
Extract numbers from strings Stan Altshuller Excel Worksheet Functions 6 February 17th 05 09:33 PM


All times are GMT +1. The time now is 08:01 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017