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
  #21  
Old July 14th, 2004, 10:16 AM
Harlan Grove
external usenet poster
 
Posts: n/a
Default finding a value in a string

"Frank Kabel" wrote...
....
Also correct that Excel converts '6.2' to a date


Which, upon reflection, is probably appropriate since '6,2' would be 6 +
2/10 in such locales.


  #22  
Old July 14th, 2004, 10:40 AM
icestationzbra
external usenet poster
 
Posts: n/a
Default finding a value in a string

can you please tell me which is the latest formula or UDF that i am
supposed to be using :-)...


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

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

lol
now you have many possibilities. I'd suggest to use Harlan's latest UDF
or Harlan's latest post. Just try them. They should work at least for
over 95% of your data (the rest then has to be corrected manually)

--
Regards
Frank Kabel
Frankfurt, Germany


can you please tell me which is the latest formula or UDF that i am
supposed to be using :-)...


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


  #24  
Old July 14th, 2004, 08:00 PM
icestationzbra
external usenet poster
 
Posts: n/a
Default finding a value in a string

i tried the UDF provided by harlan, as well as the formula provided by
frank. here are a few cases where they did not return the expected
output (which have been provided in braces). the number after '=' are
the actual outputs.

ABC_DEF_IJK_MNO_P04_1.xls = 04.1 (1)

ABC_DEF_IJK_Draft1a.1.xls = .1 (1)

ABC_DEF_TE020_OLM__Draft1B_V1-1.xls = -1 (1)

most of the correct outputs are in the format '1.0' or '2.0'. i have no
issues with these. some also had a leading space, such as ' 1'.

frank's formula:

=LOOKUP(99999,ABS(-MID(LEFT(RIGHT(SUBSTITUTE(A1,"_",".",LEN("_"&A1)-LEN(SUBSTITUTE("_"&A1,"_",""))),9),5),6-INT(seq/5),1+MOD(seq,5))))


ABC_DEF_CV070_ARC_1.0_v1.xls = 0 (1)

ABC_DEF_AP130_1.2_v1.xls = 2 (1)

ABC_DEF_ARC_1.3_V2.xls = 3 (2)

ABC_DEF_MD070_v1.0_V1.xls = 0 (1)

this was a good idea:

ABC_DEF_TE020_CAPS_1_V().xls = #N/A

mac.


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

  #25  
Old July 14th, 2004, 08:19 PM
hgrove
external usenet poster
 
Posts: n/a
Default finding a value in a string

icestationzbra wrote...
can you please tell me which is the latest formula or UDF that i
am supposed to be using


You're supposed to try them all and see what works best.

Actually, if you're going to use a UDF, then I should have mentioned
the Subst function. See the following link for its code.

http://www.google.com/groups?selm=7c...wsrange r.com

You could use it as

=subst(A1,".+[^._0-9][._]?(\d([._]\d+)?).*\.xls","$1")

to extract the version number from the filenames.

However, if you want to avoid UDFs (not a bad idea), looks like my last
formula works in a single cell as long as you're using standard US
settings.

=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))))


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

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

Hi
change the formula
=LOOKUP(99999,ABS(-MID(LEFT(RIGHT(SUBSTITUTE(A1,"_",".",LEN("_"&A1)-LEN
(SUBSTITUTE("_"&A1,"_",""))),9),5),6-INT(seq/5),1+MOD(seq,5))))

to
=LOOKUP(99999,ABS(-MID(LEFT(RIGHT(SUBSTITUTE("_" &
A1,"_",".",LEN("_"&A1)-LEN(SUBSTITUTE("_"&A1,"_",""))),9),5),6-INT(seq/
5),1+MOD(seq,5))))


--
Regards
Frank Kabel
Frankfurt, Germany


i tried the UDF provided by harlan, as well as the formula provided

by
frank. here are a few cases where they did not return the expected
output (which have been provided in braces). the number after '='

are
the actual outputs.

ABC_DEF_IJK_MNO_P04_1.xls = 04.1 (1)

ABC_DEF_IJK_Draft1a.1.xls = .1 (1)

ABC_DEF_TE020_OLM__Draft1B_V1-1.xls = -1 (1)

most of the correct outputs are in the format '1.0' or '2.0'. i have
no issues with these. some also had a leading space, such as ' 1'.

frank's formula:


=LOOKUP(99999,ABS(-MID(LEFT(RIGHT(SUBSTITUTE(A1,"_",".",LEN("_"&A1)-LEN
(SUBSTITUTE("_"&A1,"_",""))),9),5),6-INT(seq/5),1+MOD(seq,5))))


ABC_DEF_CV070_ARC_1.0_v1.xls = 0 (1)

ABC_DEF_AP130_1.2_v1.xls = 2 (1)

ABC_DEF_ARC_1.3_V2.xls = 3 (2)

ABC_DEF_MD070_v1.0_V1.xls = 0 (1)

this was a good idea:

ABC_DEF_TE020_CAPS_1_V().xls = #N/A

mac.


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


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

harlan,

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

where should i use the subst function in the UDF?

mac.


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

  #28  
Old July 14th, 2004, 08:40 PM
hgrove
external usenet poster
 
Posts: n/a
Default finding a value in a string

icestationzbra wrote...
i tried the UDF provided by harlan, as well as the formula
provided by frank. here are a few cases where they did not
return the expected output (which have been provided in
braces). the number after '=' are the actual outputs.

...

See my most recent previous response in which I mentioned the Subst
function. It's beginning to look like it's the only thing that would
work, and you might need to change the formula to

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

this was a good idea:

ABC_DEF_TE020_CAPS_1_V().xls = #N/A


But you've also written that you have filenames like

ABC_DEF123_IJK_V4_NA.xls

for which 4 is presumably the result you seek. If you want to look for
the first numeric substring to the right of '_V' when present, then the
Subst function could accomodate that, but that begs the question how to
handle

ABC_DEF123_IJK_V5_V.xls

?

There does come a point at which if enough humans are allowed to throw
enough random character variations at a computer, the computer won't be
able to detect any pattern.


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

  #29  
Old July 14th, 2004, 08:50 PM
icestationzbra
external usenet poster
 
Posts: n/a
Default finding a value in a string

frank,

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

mac.


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

  #30  
Old July 14th, 2004, 09:04 PM
Frank Kabel
external usenet poster
 
Posts: n/a
Default finding a value in a string

[....]

ABC_DEF123_IJK_V5_V.xls

?

There does come a point at which if enough humans are allowed to

throw
enough random character variations at a computer, the computer won't
be able to detect any pattern.


Ack
sometimes it's just not possible to parse such differing strings :-)

For the OP: For the future you may consider defining some name
conventions for your filenames (and maybe even restrict them). As
Harlan said there's a point completely free user entries will mess with
any type of formula/code :-)

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 10:22 AM.


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