A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

finding a value in a string



 
 
Thread Tools Display Modes
  #31  
Old July 14th, 2004, 09:06 PM
Frank Kabel
external usenet poster
 
Posts: n/a
Default finding a value in a string

Hi
as mentioned in the previous post considering your differing types of
filenames I'd assume that every formula will have a success rate below
100% (but above lets say 95% - esp. Harlan's subst UDF).

As in most cases with text parsing there will be some records you have
to parse/correct manually


--
Regards
Frank Kabel
Frankfurt, Germany


frank,

that formula results in '0.1' whenever there is a '_1' in the input
string.

mac.


---
Message posted from http://www.ExcelForum.com/


  #32  
Old July 14th, 2004, 09:19 PM
icestationzbra
external usenet poster
 
Posts: n/a
Default finding a value in a string

frank and harlan,

thank you for all you perseverance.

i will make do with all this help that you have provided. most of the
cases are covered by the udf and formula. the rest, i think i can take
care of them manually.

thanks once again,

mac.


---
Message posted from http://www.ExcelForum.com/

  #33  
Old July 15th, 2004, 03:18 AM
Harlan Grove
external usenet poster
 
Posts: n/a
Default finding a value in a string

"icestationzbra " wrote...
the latest formula provided by you, gives a #N/A for everything.


Not on my system if you mean the latest LOOKUP formula. If the following
were in A1,

ABC_DEF123_IJK_V4_NA.xls

then the formula

=LOOKUP(99999,
ABS(-MID(SUBSTITUTE(LEFT(RIGHT(A1,9),5),"_",".",
1+MOD(ROW(INDIRECT("1:50")),2)),
6-MOD(INT((ROW(INDIRECT("1:50"))-1)/5),5),
1+MOD(ROW(INDIRECT("1:50"))-1,5))))

returns 4 on my system, and all but your latest pathological filenames
return correct results.

where should i use the subst function in the UDF?


The Subst function *is* another UDF, so it's code goes into a general VBA
module, and you'd use it in the formula I gave in my previous response in
this branch. For filename in A1,

=subst(LEFT(RIGHT(A1,9),5),".*\D[._]?(\d([._]\d+)?).*","$1")


  #34  
Old July 15th, 2004, 07:51 AM
icestationzbra
external usenet poster
 
Posts: n/a
Default finding a value in a string

harlan,

about the #N/A, i will check it again to see if i am doing something
wrong.

mac.


---
Message posted from http://www.ExcelForum.com/

  #35  
Old July 16th, 2004, 09:28 PM
icestationzbra
external usenet poster
 
Posts: n/a
Default finding a value in a string

hi,

i used a function to return all the numbers from a string. from that i
picked up the rightmost string that is greater than zero. seems to be
working for me in some cases.

i am facing one problem though. if a string has no numeric characters,
i get a #VALUE!. is there a way to get a message in there, instead? can
a function return a message string ("No Number")?

thanks,

mac.

*****

Public Function GetNum(varString As Variant)
Dim i As Long, n As Long, x As String, strTemp As String
n = Len(varString) 'length of the input string
If n 1 Then Exit Function
strTemp = ""
For i = 1 To n
If IsNumeric(Mid(varString, i, 1)) Then
x = Mid(varString, i, 1) 'pick the byte if numeric
strTemp = strTemp & x 'append it to the string
End If
Next i
GetNum = CDbl(strTemp) 'convert string to long
End Function


---
Message posted from http://www.ExcelForum.com/

  #36  
Old July 16th, 2004, 09:51 PM
Myrna Larson
external usenet poster
 
Posts: n/a
Default finding a value in a string

Here's a modification.

BTW, your comment says "convert to long", but the function you used converts
to a double, in case that makes any difference.

Public Function GetNum(varString As Variant)
Dim i As Long, n As Long, x As String, strTemp As String
n = Len(varString) 'length of the input string
If n 1 Then Exit Function
strTemp = ""
For i = 1 To n
If IsNumeric(Mid(varString, i, 1)) Then
x = Mid(varString, i, 1) 'pick the byte if numeric
strTemp = strTemp & x 'append it to the string
End If
Next i
If Len(strTemp) = 0 Then
GetNum = "No digits found"
Else
GetNum = CDbl(strTemp) 'convert string to DOUBLE!!!
End If
End Function



On Fri, 16 Jul 2004 15:28:46 -0500, icestationzbra
wrote:

hi,

i used a function to return all the numbers from a string. from that i
picked up the rightmost string that is greater than zero. seems to be
working for me in some cases.

i am facing one problem though. if a string has no numeric characters,
i get a #VALUE!. is there a way to get a message in there, instead? can
a function return a message string ("No Number")?

thanks,

mac.

*****

Public Function GetNum(varString As Variant)
Dim i As Long, n As Long, x As String, strTemp As String
n = Len(varString) 'length of the input string
If n 1 Then Exit Function
strTemp = ""
For i = 1 To n
If IsNumeric(Mid(varString, i, 1)) Then
x = Mid(varString, i, 1) 'pick the byte if numeric
strTemp = strTemp & x 'append it to the string
End If
Next i
GetNum = CDbl(strTemp) 'convert string to long
End Function


---
Message posted from http://www.ExcelForum.com/


  #37  
Old July 16th, 2004, 10:43 PM
icestationzbra
external usenet poster
 
Posts: n/a
Default finding a value in a string

myrna,

thanks, that helped.

mac.


---
Message posted from http://www.ExcelForum.com/

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Question re MailMerge and VB.NET thecoiman Mailmerge 5 May 17th, 2004 04:13 PM
finding certain characters within a string within multiple cells Gav !! Worksheet Functions 1 April 15th, 2004 08:27 AM
Inserting a space into a text string Brian Anderson Worksheet Functions 1 April 6th, 2004 05:39 AM
Finding last name in a first last name string Brian Bonner Worksheet Functions 4 March 31st, 2004 09:41 PM
Finding a text string within a cell Peo Sjoblom Worksheet Functions 6 February 16th, 2004 10:52 PM


All times are GMT +1. The time now is 03:52 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.