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
  #1  
Old July 13th, 2004, 07:10 PM
icestationzbra
external usenet poster
 
Posts: n/a
Default finding a value in a string

i am looking for some help in finding a particular value in a string.

i have strings that look like:

ABC_DEF123_IJK_V(1).xls

or

ABC_DEF123_IJK_V2.xls

or

ABC_DEF123_IJK_V3_A.xls

or

ABC_DEF123_IJK_V4_NA.xls

or

ABC_DEF123_IJK_V5.0.xls

or

ABC_DEF123_IJK_6_0.xls

or

ABC DEF123 IJK V7.xls

these are the filenames of various files that users would have created
and made an entry into this one master sheet along a column. i have as
many nomenclatures as i have users.

i need to extract the (1, 2, 3, 4, 5, 6, 7) from the strings. they
denote a certain value that is of importance to other calculations.

now, i have created upto 5 columns with RIGHT, MID, LEFT and WHAT HAVE
YOU and WHAT NOT to extricate the values from the strings. i have over
3000 rows from which to extract data. i have even combined upto 3 IF's
to get the desired output in some cases. only problem is, i have more
variety than the number of IFs allowed (7) incorporating ISNUMBER,
VALUE and ISTEXT functions. actually, after the 3rd IF, the formula has
more number of braces than characters!!! i am not able to create a
single formula that would take care of all the variety i have. over 5-6
columns, and even after that, a little bit of manual tweaking, allows
me to get the right value. however, it is quite cumbersome.

would anyone be able to give me one formula that could pick that number
for me from the string? there is one commonality in those strings. the
number that i require to be picked up resides within 5 places to the
left of the ".xls".

thanks in advance,

mac.


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

  #2  
Old July 13th, 2004, 07:39 PM
Ron Rosenfeld
external usenet poster
 
Posts: n/a
Default finding a value in a string

On Tue, 13 Jul 2004 13:10:24 -0500, icestationzbra
wrote:

would anyone be able to give me one formula that could pick that number
for me from the string?


It's not clear from your posting what kind of output you want.

But here is a UDF (User defined function) that extracts all of the digits as a
number.

If you have a specific output in mind, please post it.

=================================
Function GetValue(str)
Dim N As Integer, i As String
i = ""
For N = 1 To Len(str)
If IsNumeric(Mid(str, N, 1)) Then
i = i & Mid(str, N, 1)
End If
Next
If i = "" Then
GetValue = i
Exit Function
End If
GetValue = CDbl(i)
End Function
========================

To enter this, alt-F11 opens the VB Editor.

Ensure your project is highlighted in the Project Explorer, then Insert/Module
and paste the code into the window that opens.

Use it in your worksheet like any function. e.g. =getvalue(A1)


--ron
  #3  
Old July 13th, 2004, 07:39 PM
Frank Kabel
external usenet poster
 
Posts: n/a
Default finding a value in a string

Hi
try the following UDF:
Public Function Get_Numerics(rng As Range) As Double
Dim i As Integer
Dim res
Dim sValue
sValue = Mid(rng.Value, Len(rng.Value) - 9, 5)
For i = 1 To Len(sValue)
If IsNumeric(Mid(sValue, i, 1)) Then
res = res & Mid(sValue, i, 1)
End If
Next i
res = CDbl(res)
Get_Numerics = res
End Function

and use it like
=GET_NUMERICS(A1)


--
Regards
Frank Kabel
Frankfurt, Germany


i am looking for some help in finding a particular value in a string.

i have strings that look like:

ABC_DEF123_IJK_V(1).xls

or

ABC_DEF123_IJK_V2.xls

or

ABC_DEF123_IJK_V3_A.xls

or

ABC_DEF123_IJK_V4_NA.xls

or

ABC_DEF123_IJK_V5.0.xls

or

ABC_DEF123_IJK_6_0.xls

or

ABC DEF123 IJK V7.xls

these are the filenames of various files that users would have

created
and made an entry into this one master sheet along a column. i have

as
many nomenclatures as i have users.

i need to extract the (1, 2, 3, 4, 5, 6, 7) from the strings. they
denote a certain value that is of importance to other calculations.

now, i have created upto 5 columns with RIGHT, MID, LEFT and WHAT

HAVE
YOU and WHAT NOT to extricate the values from the strings. i have

over
3000 rows from which to extract data. i have even combined upto 3

IF's
to get the desired output in some cases. only problem is, i have more
variety than the number of IFs allowed (7) incorporating ISNUMBER,
VALUE and ISTEXT functions. actually, after the 3rd IF, the formula
has more number of braces than characters!!! i am not able to create

a
single formula that would take care of all the variety i have. over
5-6 columns, and even after that, a little bit of manual tweaking,
allows me to get the right value. however, it is quite cumbersome.

would anyone be able to give me one formula that could pick that
number for me from the string? there is one commonality in those
strings. the number that i require to be picked up resides within 5
places to the left of the ".xls".

thanks in advance,

mac.


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


  #4  
Old July 13th, 2004, 08:05 PM
icestationzbra
external usenet poster
 
Posts: n/a
Default finding a value in a string

sorry if i was not clear in my earlier posting.

if you see the filenames that i have mentioned, there is a number
within the last 5-6 characters. thats the only number that keeps
changing (1, 2, 3, 4, 5, 6, 7) within all the filenames that i have
given. thats a sort of version number and thats what i am looking to
extract.

for example, in "ABC DEF123 IJK V7.xls", '7' is what i am looking to
extract. in "ABC_DEF123_IJK_V5.0.xls", '5' is what i am looking to
extract.

if the UDF extracts everything from the string, i might still have to
do some drudgery. some filenames are as long as 100 characters with
several numbers in them.

however, there is one condition that almost 95% of the strings would
adhere to. to the left of ".xls", if the first numeric character that
is greater than zero could be picked, that would be the number i am
looking for.

please help.


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

  #5  
Old July 13th, 2004, 08:21 PM
Frank Kabel
external usenet poster
 
Posts: n/a
Default finding a value in a string

Hi
have you tried the UDF?. It should return only this version number. At
least it worked for your example data?
If not what problems did you encounter (please post the example
filename in these cases)

--
Regards
Frank Kabel
Frankfurt, Germany


sorry if i was not clear in my earlier posting.

if you see the filenames that i have mentioned, there is a number
within the last 5-6 characters. thats the only number that keeps
changing (1, 2, 3, 4, 5, 6, 7) within all the filenames that i have
given. thats a sort of version number and thats what i am looking to
extract.

for example, in "ABC DEF123 IJK V7.xls", '7' is what i am looking to
extract. in "ABC_DEF123_IJK_V5.0.xls", '5' is what i am looking to
extract.

if the UDF extracts everything from the string, i might still have to
do some drudgery. some filenames are as long as 100 characters with
several numbers in them.

however, there is one condition that almost 95% of the strings would
adhere to. to the left of ".xls", if the first numeric character that
is greater than zero could be picked, that would be the number i am
looking for.

please help.


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


  #6  
Old July 13th, 2004, 08:37 PM
icestationzbra
external usenet poster
 
Posts: n/a
Default finding a value in a string

frank,

i had tried your udf.

here are two strings that i tested it for, wherein it failed.

ABC_DEF_IJK_MNO070_UTHI05_1.1_V1.xls

the udf returned 11, the expected output is 1.

ABC_DEF_IJK_MNO120_QTLY19BREP_1.xls

the udf returned 0, the expected output is 1.

there are other cases wherein it gave the correct output.

thank you for your response and help.

mac.


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

  #7  
Old July 13th, 2004, 09:06 PM
Frank Kabel
external usenet poster
 
Posts: n/a
Default finding a value in a string

Hi
in the UDF change the line
sValue = Mid(rng.Value, Len(rng.Value) - 9, 5)

to
sValue = Mid(rng.Value, Len(rng.Value) - 8, 5)

This solves your second example. The first example is difficult as you
said that the version number is within the last 5 characters before
'.xls'. But in this example there's also a part of a previous number
within this range. To solve also this problem try the following UDF:
Public Function Get_Numerics(rng As Range) As Double
Dim i As Integer
Dim res
Dim sValue
Dim version
version = False
sValue = Mid(rng.Value, Len(rng.Value) - 8, 5)
For i = Len(sValue) To 1 Step -1
If IsNumeric(Mid(sValue, i, 1)) Then
version = i
res = Mid(sValue, version, 1)
While IsNumeric("0" & Mid(sValue, version - 1, 1) & res)
version = version - 1
res = Mid(sValue, version, 1) & res
Wend
Exit For
End If
Next i
res = CDbl(res)
Get_Numerics = res
End Function

Not fully tested but give it a try


--
Regards
Frank Kabel
Frankfurt, Germany


frank,

i had tried your udf.

here are two strings that i tested it for, wherein it failed.

ABC_DEF_IJK_MNO070_UTHI05_1.1_V1.xls

the udf returned 11, the expected output is 1.

ABC_DEF_IJK_MNO120_QTLY19BREP_1.xls

the udf returned 0, the expected output is 1.

there are other cases wherein it gave the correct output.

thank you for your response and help.

mac.


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


  #8  
Old July 13th, 2004, 09:42 PM
hgrove
external usenet poster
 
Posts: n/a
Default finding a value in a string

Frank Kabel wrote...
have you tried the UDF?. It should return only this version
number. At least it worked for your example data?

...

*YOU* didn't test your UDF on the filenames

ABC_DEF123_IJK_V2.xls

or

ABC DEF123 IJK V7.xls

for which your UDF returns 0. Why? Classic off-by-one indexing error.
The statement

sValue = Mid(rng.Value, Len(rng.Value) - 9, 5)

would need to be replaced with

sValue = Mid(rng.Value, Len(rng.Value) - 8, 5)

in order to pick up version numbers immediately preceding '.xls', but
that exposes another failing of your UDF. If the change above were
made, then for the filenames

ABC_DEF123_IJK_V5.0.xls

and

ABC_DEF123_IJK_6_0.xls

it returns 50 and 60, respectively, because you're using a character at
a time algorithm that's clueless about '.' being a valid numeric
character as long as it appears once with at least one adjacent
numeral. Even then it'd choke on '6_0', which appears to be a valid
alternative to 6.0.

Anyway, a UDF that copes with all the OP's variations given so far
needs to locate the rightmost numeric substring, and that requires at
least two loops - first to find the end of the rightmost numeric
substring, then to find it's beginning. Here's an alternative UDF
that's more permissive with its argument and returns a string rather
than a number, so strings without such numeric substrings return ""
rather than #VALUE!.


Public Function vn(ByVal v As Variant) As String
Dim i As Long, n As Long, s As String, t As String

n = Len(v) - 4

If n = 0 Then Exit Function 'v is too short


For i = 0 To 4

If Mid(v, n - i, 1) Like "[0-9]" Then
v = Mid(v, n - 4, 5 - i)
n = 5 - i
Exit For
End If

Next i


If i 4 Then Exit Function


For i = n - 1 To 0 Step -1
s = Right(v, n - i)
t = Application.WorksheetFunction.Substitute(s, "_", ".")

If IsNumeric(s) Then
vn = s

ElseIf IsNumeric(t) Then
vn = t

Else
Exit For

End If

Next i


End Function


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

  #9  
Old July 13th, 2004, 10:06 PM
hgrove
external usenet poster
 
Posts: n/a
Default finding a value in a string

icestationzbra wrote...
...
i have strings that look like:

ABC_DEF123_IJK_V(1).xls

...
ABC_DEF123_IJK_V2.xls

...
ABC_DEF123_IJK_V3_A.xls

...
ABC_DEF123_IJK_V4_NA.xls

...
ABC_DEF123_IJK_V5.0.xls

...
ABC_DEF123_IJK_6_0.xls

...
ABC DEF123 IJK V7.xls

these are the filenames of various files that users would have
created and made an entry into this one master sheet along a
column. i have as many nomenclatures as i have users.

i need to extract the (1, 2, 3, 4, 5, 6, 7) from the strings. they
denote a certain value that is of importance to other
calculations.

...
would anyone be able to give me one formula that could pick
that number for me from the string? there is one commonality in
those strings. the number that i require to be picked up resides
within 5 places to the left of the ".xls".


If it weren't for the filename

ABC_DEF123_IJK_6_0.xls

you could use the formula

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

to pull the version number from the filename in cell A1. I don't think
there's any compact way to handle underscores between numerals as
decimal points, but I could be wrong.


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

  #10  
Old July 13th, 2004, 10:07 PM
Frank Kabel
external usenet poster
 
Posts: n/a
Default finding a value in a string

Frank Kabel wrote...
have you tried the UDF?. It should return only this version
number. At least it worked for your example data?

..

*YOU* didn't test your UDF on the filenames


Ack, got me :-)

[....]
Anyway, a UDF that copes with all the OP's variations given so far
needs to locate the rightmost numeric substring, and that requires at
least two loops - first to find the end of the rightmost numeric
substring, then to find it's beginning. Here's an alternative UDF
that's more permissive with its argument and returns a string rather
than a number, so strings without such numeric substrings return ""
rather than #VALUE!.


like your approach. And your UDF also covers 5_5 (what my seond UDF
does not)


Frank

 




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 04:43 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.