Text format

Hy,

I have an text how looks like this: "AA-BB CC-dd". I want to select the text until the "-dd", so the result would be "AA-BB CC". So the text until "-" followed by an lower case letter. How can I do that ?
0
Me8786 (90)
8/3/2004 4:57:02 AM
excel.misc 78881 articles. 5 followers. Follow

10 Replies
452 Views

Similar Articles

[PageSpeed] 31

Hi
is this text part always after the last '-'?

--
Regards
Frank Kabel
Frankfurt, Germany


Me wrote:
> Hy,
>
> I have an text how looks like this: "AA-BB CC-dd". I want to select
> the text until the "-dd", so the result would be "AA-BB CC". So the
> text until "-" followed by an lower case letter. How can I do that ?

0
frank.kabel (11126)
8/3/2004 5:29:33 AM
Yes, the lower case letters are preceded by "-"

Here is what I think:
1.SEARCH("-";A1;1) ---> result 3
2. Test character 4(3+1) from A1 with CODE() to get the ascii code
3. Lower case letters have the ascii code >=97, so if code <97 I will search the A1 text from character 4(or 5) to the end and see if there are any more "-" characters.
4. The second "-"have the search result  9 and I'll test character 10 who have the ascii code >=97. So I'll stop and copy A1 from 1-st to 8(9-1) characters to result "AA-BB CC"
 
     Can you tell me how to get for example the 3-rd character from A1 text ?
"Frank Kabel" wrote:

> Hi
> is this text part always after the last '-'?
> 
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
> 
> 
> Me wrote:
> > Hy,
> >
> > I have an text how looks like this: "AA-BB CC-dd". I want to select
> > the text until the "-dd", so the result would be "AA-BB CC". So the
> > text until "-" followed by an lower case letter. How can I do that ?
> 
> 
0
Me8786 (90)
8/3/2004 5:53:01 AM
Hi
the question was if the lower case letters are ALWAYS 
AFTER the last '-'. That is the following string is not in 
your data list
AAA-ascv-DD

If this is not an allowed entry it would reduce the 
formula complexity

>-----Original Message-----
>Yes, the lower case letters are preceded by "-"
>
>Here is what I think:
>1.SEARCH("-";A1;1) ---> result 3
>2. Test character 4(3+1) from A1 with CODE() to get the 
ascii code
>3. Lower case letters have the ascii code >=97, so if 
code <97 I will search the A1 text from character 4(or 5) 
to the end and see if there are any more "-" characters.
>4. The second "-"have the search result  9 and I'll test 
character 10 who have the ascii code >=97. So I'll stop 
and copy A1 from 1-st to 8(9-1) characters to result "AA-
BB CC"
> 
>     Can you tell me how to get for example the 3-rd 
character from A1 text ?
>"Frank Kabel" wrote:
>
>> Hi
>> is this text part always after the last '-'?
>> 
>> --
>> Regards
>> Frank Kabel
>> Frankfurt, Germany
>> 
>> 
>> Me wrote:
>> > Hy,
>> >
>> > I have an text how looks like this: "AA-BB CC-dd". I 
want to select
>> > the text until the "-dd", so the result would be "AA-
BB CC". So the
>> > text until "-" followed by an lower case letter. How 
can I do that ?
>> 
>> 
>.
>
0
frank.kabel (11126)
8/3/2004 6:35:48 AM
Yes, I don't have upper case letters after the lower case letters, so AAA-ascv-DD is not an case. Even if will be I don't need that text. I only need the text fromthe start to "-" followed by an lower case letter. I fond how to return the 3+1 letter : =MID(A1;SEARCH("-";A1;1)+1;1) ---> B.

"Frank Kabel" wrote:

> Hi
> the question was if the lower case letters are ALWAYS 
> AFTER the last '-'. That is the following string is not in 
> your data list
> AAA-ascv-DD
> 
> If this is not an allowed entry it would reduce the 
> formula complexity
> 
> >-----Original Message-----
> >Yes, the lower case letters are preceded by "-"
> >
> >Here is what I think:
> >1.SEARCH("-";A1;1) ---> result 3
> >2. Test character 4(3+1) from A1 with CODE() to get the 
> ascii code
> >3. Lower case letters have the ascii code >=97, so if 
> code <97 I will search the A1 text from character 4(or 5) 
> to the end and see if there are any more "-" characters.
> >4. The second "-"have the search result  9 and I'll test 
> character 10 who have the ascii code >=97. So I'll stop 
> and copy A1 from 1-st to 8(9-1) characters to result "AA-
> BB CC"
> > 
> >     Can you tell me how to get for example the 3-rd 
> character from A1 text ?
> >"Frank Kabel" wrote:
> >
> >> Hi
> >> is this text part always after the last '-'?
> >> 
> >> --
> >> Regards
> >> Frank Kabel
> >> Frankfurt, Germany
> >> 
> >> 
> >> Me wrote:
> >> > Hy,
> >> >
> >> > I have an text how looks like this: "AA-BB CC-dd". I 
> want to select
> >> > the text until the "-dd", so the result would be "AA-
> BB CC". So the
> >> > text until "-" followed by an lower case letter. How 
> can I do that ?
> >> 
> >> 
> >.
> >
> 
0
Me8786 (90)
8/3/2004 6:45:01 AM
Hi
try the following formula for cell A1:

=IF(EXACT(MID(A1,LOOKUP(2,1/(MID(A1,ROW(INDIRECT
("1:1024")),1)="-"),ROW(INDIRECT("1:1024")))+1,1),UPPER(MID
(A1,LOOKUP(2,1/(MID(A1,ROW(INDIRECT("1:1024")),1)="-"),ROW
(INDIRECT("1:1024")))+1,1))),"",LEFT(A1,LOOKUP(2,1/(MID
(A1,ROW(INDIRECT("1:1024")),1)="-"),ROW(INDIRECT
("1:1024")))))


Note: It does return an #NA error if no '-' exist. you can 
shorten this formula if you do the following:
1. Define a name called 'seq' in the menu 'Insert - Name - 
Define' and assign the following formula to it:
=ROW(INDIRECT("1:1024"))

2. Use the following formula
=IF(EXACT(MID(A1,LOOKUP(2,1/(MID(A1,seq,1)="-"),seq)
+1,1),UPPER(MID(A1,LOOKUP(2,1/(MID(A1,seq,1)="-"),seq)
+1,1))),"",LEFT(A1,LOOKUP(2,1/(MID(A1,seq,1)="-"),seq)))




>-----Original Message-----
>Yes, I don't have upper case letters after the lower case 
letters, so AAA-ascv-DD is not an case. Even if will be I 
don't need that text. I only need the text fromthe start 
to "-" followed by an lower case letter. I fond how to 
return the 3+1 letter : =MID(A1;SEARCH("-";A1;1)+1;1) ---> 
B.
>
>"Frank Kabel" wrote:
>
>> Hi
>> the question was if the lower case letters are ALWAYS 
>> AFTER the last '-'. That is the following string is not 
in 
>> your data list
>> AAA-ascv-DD
>> 
>> If this is not an allowed entry it would reduce the 
>> formula complexity
>> 
>> >-----Original Message-----
>> >Yes, the lower case letters are preceded by "-"
>> >
>> >Here is what I think:
>> >1.SEARCH("-";A1;1) ---> result 3
>> >2. Test character 4(3+1) from A1 with CODE() to get 
the 
>> ascii code
>> >3. Lower case letters have the ascii code >=97, so if 
>> code <97 I will search the A1 text from character 4(or 
5) 
>> to the end and see if there are any more "-" characters.
>> >4. The second "-"have the search result  9 and I'll 
test 
>> character 10 who have the ascii code >=97. So I'll stop 
>> and copy A1 from 1-st to 8(9-1) characters to 
result "AA-
>> BB CC"
>> > 
>> >     Can you tell me how to get for example the 3-rd 
>> character from A1 text ?
>> >"Frank Kabel" wrote:
>> >
>> >> Hi
>> >> is this text part always after the last '-'?
>> >> 
>> >> --
>> >> Regards
>> >> Frank Kabel
>> >> Frankfurt, Germany
>> >> 
>> >> 
>> >> Me wrote:
>> >> > Hy,
>> >> >
>> >> > I have an text how looks like this: "AA-BB CC-dd". 
I 
>> want to select
>> >> > the text until the "-dd", so the result would 
be "AA-
>> BB CC". So the
>> >> > text until "-" followed by an lower case letter. 
How 
>> can I do that ?
>> >> 
>> >> 
>> >.
>> >
>> 
>.
>
0
frank.kabel (11126)
8/3/2004 7:45:15 AM
Hi,

Try the following formula:

   =IF(COUNTIF(A1,"*-* *-*"),
    IF(AND(CODE(MID(A1,FIND("-",A1,FIND("-",A1)+1)+1,1))>=97,
    CODE(MID(A1,FIND("-",A1,FIND("-",A1)+1)+1,1))<=122),
    LEFT(A1,FIND("-",A1,FIND("-",A1)+1)-1),""),"")


--
Regards,
Soo Cheon Jheong
_  _
^��^
 -- 


0
exceler (34)
8/3/2004 8:33:05 AM
Not working in all the cases. For example 
A1="AA-B" ---> "AA-B"
A1="AA" ---> "AA"
A1="AA-a-SS" ----> "AA"

It is posible to be only one "-" ore none. But I have an looooong formula for this cases:

=IF(IF(ISLOGICAL(CODE(MID(A1;SEARCH("-";A1;1)+1;1))>96);CODE(MID(A1;SEARCH("-";A1;1)+1;1))>96;FALSE);LEFT(A1;SEARCH("-";A1;1)-1);IF(IF(ISLOGICAL(CODE(MID(A1;SEARCH("-";A1;SEARCH("-";A1;1)+1)+1;1))>96);CODE(MID(A1;SEARCH("-";A1;SEARCH("-";A1;1)+1)+1;1))>96;FALSE);LEFT(A1;SEARCH("-";A1;SEARCH("-";A1;1)+1)-1);A1))


"Frank Kabel" wrote:

> Hi
> try the following formula for cell A1:
> 
> =IF(EXACT(MID(A1,LOOKUP(2,1/(MID(A1,ROW(INDIRECT
> ("1:1024")),1)="-"),ROW(INDIRECT("1:1024")))+1,1),UPPER(MID
> (A1,LOOKUP(2,1/(MID(A1,ROW(INDIRECT("1:1024")),1)="-"),ROW
> (INDIRECT("1:1024")))+1,1))),"",LEFT(A1,LOOKUP(2,1/(MID
> (A1,ROW(INDIRECT("1:1024")),1)="-"),ROW(INDIRECT
> ("1:1024")))))
> 
> 
> Note: It does return an #NA error if no '-' exist. you can 
> shorten this formula if you do the following:
> 1. Define a name called 'seq' in the menu 'Insert - Name - 
> Define' and assign the following formula to it:
> =ROW(INDIRECT("1:1024"))
> 
> 2. Use the following formula
> =IF(EXACT(MID(A1,LOOKUP(2,1/(MID(A1,seq,1)="-"),seq)
> +1,1),UPPER(MID(A1,LOOKUP(2,1/(MID(A1,seq,1)="-"),seq)
> +1,1))),"",LEFT(A1,LOOKUP(2,1/(MID(A1,seq,1)="-"),seq)))
> 
> 
> 
> 
> >-----Original Message-----
> >Yes, I don't have upper case letters after the lower case 
> letters, so AAA-ascv-DD is not an case. Even if will be I 
> don't need that text. I only need the text fromthe start 
> to "-" followed by an lower case letter. I fond how to 
> return the 3+1 letter : =MID(A1;SEARCH("-";A1;1)+1;1) ---> 
> B.
> >
> >"Frank Kabel" wrote:
> >
> >> Hi
> >> the question was if the lower case letters are ALWAYS 
> >> AFTER the last '-'. That is the following string is not 
> in 
> >> your data list
> >> AAA-ascv-DD
> >> 
> >> If this is not an allowed entry it would reduce the 
> >> formula complexity
> >> 
> >> >-----Original Message-----
> >> >Yes, the lower case letters are preceded by "-"
> >> >
> >> >Here is what I think:
> >> >1.SEARCH("-";A1;1) ---> result 3
> >> >2. Test character 4(3+1) from A1 with CODE() to get 
> the 
> >> ascii code
> >> >3. Lower case letters have the ascii code >=97, so if 
> >> code <97 I will search the A1 text from character 4(or 
> 5) 
> >> to the end and see if there are any more "-" characters.
> >> >4. The second "-"have the search result  9 and I'll 
> test 
> >> character 10 who have the ascii code >=97. So I'll stop 
> >> and copy A1 from 1-st to 8(9-1) characters to 
> result "AA-
> >> BB CC"
> >> > 
> >> >     Can you tell me how to get for example the 3-rd 
> >> character from A1 text ?
> >> >"Frank Kabel" wrote:
> >> >
> >> >> Hi
> >> >> is this text part always after the last '-'?
> >> >> 
> >> >> --
> >> >> Regards
> >> >> Frank Kabel
> >> >> Frankfurt, Germany
> >> >> 
> >> >> 
> >> >> Me wrote:
> >> >> > Hy,
> >> >> >
> >> >> > I have an text how looks like this: "AA-BB CC-dd". 
> I 
> >> want to select
> >> >> > the text until the "-dd", so the result would 
> be "AA-
> >> BB CC". So the
> >> >> > text until "-" followed by an lower case letter. 
> How 
> >> can I do that ?
> >> >> 
> >> >> 
> >> >.
> >> >
> >> 
> >.
> >
> 
0
Me8786 (90)
8/3/2004 9:03:01 AM
Hi
try
=IF(ISNA(LOOKUP(2,1/(MID(A1,seq,1)="-"),seq)),A1,IF(EXACT
(MID(A1,LOOKUP(2,1/(MID(A1,seq,1)="-"),seq)+1,1),UPPER(MID
(A1,LOOKUP(2,1/(MID(A1,seq,1)="-"),seq)+1,1))),A1,LEFT
(A1,LOOKUP(2,1/(MID(A1,seq,1)="-"),seq))))

This solves the issue for examples 1 and 2.
Example 3 is according to your previous post NOT a valid 
entry. So the formula would return
AA-a-SS

as you said after the small case no upper case can occur

>-----Original Message-----
>Not working in all the cases. For example 
>A1="AA-B" ---> "AA-B"
>A1="AA" ---> "AA"
>A1="AA-a-SS" ----> "AA"
>
>It is posible to be only one "-" ore none. But I have an 
looooong formula for this cases:
>
>=IF(IF(ISLOGICAL(CODE(MID(A1;SEARCH("-";A1;1)+1;1))
>96);CODE(MID(A1;SEARCH("-";A1;1)+1;1))>96;FALSE);LEFT
(A1;SEARCH("-";A1;1)-1);IF(IF(ISLOGICAL(CODE(MID(A1;SEARCH
("-";A1;SEARCH("-";A1;1)+1)+1;1))>96);CODE(MID(A1;SEARCH("-
";A1;SEARCH("-";A1;1)+1)+1;1))>96;FALSE);LEFT(A1;SEARCH("-
";A1;SEARCH("-";A1;1)+1)-1);A1))
>
>
>"Frank Kabel" wrote:
>
>> Hi
>> try the following formula for cell A1:
>> 
>> =IF(EXACT(MID(A1,LOOKUP(2,1/(MID(A1,ROW(INDIRECT
>> ("1:1024")),1)="-"),ROW(INDIRECT("1:1024")))+1,1),UPPER
(MID
>> (A1,LOOKUP(2,1/(MID(A1,ROW(INDIRECT("1:1024")),1)="-
"),ROW
>> (INDIRECT("1:1024")))+1,1))),"",LEFT(A1,LOOKUP(2,1/(MID
>> (A1,ROW(INDIRECT("1:1024")),1)="-"),ROW(INDIRECT
>> ("1:1024")))))
>> 
>> 
>> Note: It does return an #NA error if no '-' exist. you 
can 
>> shorten this formula if you do the following:
>> 1. Define a name called 'seq' in the menu 'Insert - 
Name - 
>> Define' and assign the following formula to it:
>> =ROW(INDIRECT("1:1024"))
>> 
>> 2. Use the following formula
>> =IF(EXACT(MID(A1,LOOKUP(2,1/(MID(A1,seq,1)="-"),seq)
>> +1,1),UPPER(MID(A1,LOOKUP(2,1/(MID(A1,seq,1)="-"),seq)
>> +1,1))),"",LEFT(A1,LOOKUP(2,1/(MID(A1,seq,1)="-"),seq)))
>> 
>> 
>> 
>> 
>> >-----Original Message-----
>> >Yes, I don't have upper case letters after the lower 
case 
>> letters, so AAA-ascv-DD is not an case. Even if will be 
I 
>> don't need that text. I only need the text fromthe 
start 
>> to "-" followed by an lower case letter. I fond how to 
>> return the 3+1 letter : =MID(A1;SEARCH("-";A1;1)+1;1) --
-> 
>> B.
>> >
>> >"Frank Kabel" wrote:
>> >
>> >> Hi
>> >> the question was if the lower case letters are 
ALWAYS 
>> >> AFTER the last '-'. That is the following string is 
not 
>> in 
>> >> your data list
>> >> AAA-ascv-DD
>> >> 
>> >> If this is not an allowed entry it would reduce the 
>> >> formula complexity
>> >> 
>> >> >-----Original Message-----
>> >> >Yes, the lower case letters are preceded by "-"
>> >> >
>> >> >Here is what I think:
>> >> >1.SEARCH("-";A1;1) ---> result 3
>> >> >2. Test character 4(3+1) from A1 with CODE() to get 
>> the 
>> >> ascii code
>> >> >3. Lower case letters have the ascii code >=97, so 
if 
>> >> code <97 I will search the A1 text from character 4
(or 
>> 5) 
>> >> to the end and see if there are any more "-" 
characters.
>> >> >4. The second "-"have the search result  9 and I'll 
>> test 
>> >> character 10 who have the ascii code >=97. So I'll 
stop 
>> >> and copy A1 from 1-st to 8(9-1) characters to 
>> result "AA-
>> >> BB CC"
>> >> > 
>> >> >     Can you tell me how to get for example the 3-
rd 
>> >> character from A1 text ?
>> >> >"Frank Kabel" wrote:
>> >> >
>> >> >> Hi
>> >> >> is this text part always after the last '-'?
>> >> >> 
>> >> >> --
>> >> >> Regards
>> >> >> Frank Kabel
>> >> >> Frankfurt, Germany
>> >> >> 
>> >> >> 
>> >> >> Me wrote:
>> >> >> > Hy,
>> >> >> >
>> >> >> > I have an text how looks like this: "AA-BB CC-
dd". 
>> I 
>> >> want to select
>> >> >> > the text until the "-dd", so the result would 
>> be "AA-
>> >> BB CC". So the
>> >> >> > text until "-" followed by an lower case 
letter. 
>> How 
>> >> can I do that ?
>> >> >> 
>> >> >> 
>> >> >.
>> >> >
>> >> 
>> >.
>> >
>> 
>.
>
0
frank.kabel (11126)
8/3/2004 9:20:29 AM
Thanks, it's working now. Hard work with excel :)

"Frank Kabel" wrote:

> Hi
> try
> =IF(ISNA(LOOKUP(2,1/(MID(A1,seq,1)="-"),seq)),A1,IF(EXACT
> (MID(A1,LOOKUP(2,1/(MID(A1,seq,1)="-"),seq)+1,1),UPPER(MID
> (A1,LOOKUP(2,1/(MID(A1,seq,1)="-"),seq)+1,1))),A1,LEFT
> (A1,LOOKUP(2,1/(MID(A1,seq,1)="-"),seq))))
> 
> This solves the issue for examples 1 and 2.
> Example 3 is according to your previous post NOT a valid 
> entry. So the formula would return
> AA-a-SS
> 
> as you said after the small case no upper case can occur
> 
> >-----Original Message-----
> >Not working in all the cases. For example 
> >A1="AA-B" ---> "AA-B"
> >A1="AA" ---> "AA"
> >A1="AA-a-SS" ----> "AA"
> >
> >It is posible to be only one "-" ore none. But I have an 
> looooong formula for this cases:
> >
> >=IF(IF(ISLOGICAL(CODE(MID(A1;SEARCH("-";A1;1)+1;1))
> >96);CODE(MID(A1;SEARCH("-";A1;1)+1;1))>96;FALSE);LEFT
> (A1;SEARCH("-";A1;1)-1);IF(IF(ISLOGICAL(CODE(MID(A1;SEARCH
> ("-";A1;SEARCH("-";A1;1)+1)+1;1))>96);CODE(MID(A1;SEARCH("-
> ";A1;SEARCH("-";A1;1)+1)+1;1))>96;FALSE);LEFT(A1;SEARCH("-
> ";A1;SEARCH("-";A1;1)+1)-1);A1))
> >
> >
> >"Frank Kabel" wrote:
> >
> >> Hi
> >> try the following formula for cell A1:
> >> 
> >> =IF(EXACT(MID(A1,LOOKUP(2,1/(MID(A1,ROW(INDIRECT
> >> ("1:1024")),1)="-"),ROW(INDIRECT("1:1024")))+1,1),UPPER
> (MID
> >> (A1,LOOKUP(2,1/(MID(A1,ROW(INDIRECT("1:1024")),1)="-
> "),ROW
> >> (INDIRECT("1:1024")))+1,1))),"",LEFT(A1,LOOKUP(2,1/(MID
> >> (A1,ROW(INDIRECT("1:1024")),1)="-"),ROW(INDIRECT
> >> ("1:1024")))))
> >> 
> >> 
> >> Note: It does return an #NA error if no '-' exist. you 
> can 
> >> shorten this formula if you do the following:
> >> 1. Define a name called 'seq' in the menu 'Insert - 
> Name - 
> >> Define' and assign the following formula to it:
> >> =ROW(INDIRECT("1:1024"))
> >> 
> >> 2. Use the following formula
> >> =IF(EXACT(MID(A1,LOOKUP(2,1/(MID(A1,seq,1)="-"),seq)
> >> +1,1),UPPER(MID(A1,LOOKUP(2,1/(MID(A1,seq,1)="-"),seq)
> >> +1,1))),"",LEFT(A1,LOOKUP(2,1/(MID(A1,seq,1)="-"),seq)))
> >> 
> >> 
> >> 
> >> 
> >> >-----Original Message-----
> >> >Yes, I don't have upper case letters after the lower 
> case 
> >> letters, so AAA-ascv-DD is not an case. Even if will be 
> I 
> >> don't need that text. I only need the text fromthe 
> start 
> >> to "-" followed by an lower case letter. I fond how to 
> >> return the 3+1 letter : =MID(A1;SEARCH("-";A1;1)+1;1) --
> -> 
> >> B.
> >> >
> >> >"Frank Kabel" wrote:
> >> >
> >> >> Hi
> >> >> the question was if the lower case letters are 
> ALWAYS 
> >> >> AFTER the last '-'. That is the following string is 
> not 
> >> in 
> >> >> your data list
> >> >> AAA-ascv-DD
> >> >> 
> >> >> If this is not an allowed entry it would reduce the 
> >> >> formula complexity
> >> >> 
> >> >> >-----Original Message-----
> >> >> >Yes, the lower case letters are preceded by "-"
> >> >> >
> >> >> >Here is what I think:
> >> >> >1.SEARCH("-";A1;1) ---> result 3
> >> >> >2. Test character 4(3+1) from A1 with CODE() to get 
> >> the 
> >> >> ascii code
> >> >> >3. Lower case letters have the ascii code >=97, so 
> if 
> >> >> code <97 I will search the A1 text from character 4
> (or 
> >> 5) 
> >> >> to the end and see if there are any more "-" 
> characters.
> >> >> >4. The second "-"have the search result  9 and I'll 
> >> test 
> >> >> character 10 who have the ascii code >=97. So I'll 
> stop 
> >> >> and copy A1 from 1-st to 8(9-1) characters to 
> >> result "AA-
> >> >> BB CC"
> >> >> > 
> >> >> >     Can you tell me how to get for example the 3-
> rd 
> >> >> character from A1 text ?
> >> >> >"Frank Kabel" wrote:
> >> >> >
> >> >> >> Hi
> >> >> >> is this text part always after the last '-'?
> >> >> >> 
> >> >> >> --
> >> >> >> Regards
> >> >> >> Frank Kabel
> >> >> >> Frankfurt, Germany
> >> >> >> 
> >> >> >> 
> >> >> >> Me wrote:
> >> >> >> > Hy,
> >> >> >> >
> >> >> >> > I have an text how looks like this: "AA-BB CC-
> dd". 
> >> I 
> >> >> want to select
> >> >> >> > the text until the "-dd", so the result would 
> >> be "AA-
> >> >> BB CC". So the
> >> >> >> > text until "-" followed by an lower case 
> letter. 
> >> How 
> >> >> can I do that ?
> >> >> >> 
> >> >> >> 
> >> >> >.
> >> >> >
> >> >> 
> >> >.
> >> >
> >> 
> >.
> >
> 
0
Me8786 (90)
8/3/2004 9:49:01 AM
Hi

FWIW here's an alternative:

=IF(ISERROR(SEARCH("-",A1)),A1,IF(CODE(RIGHT(A1,LEN(A1)-SEARCH(
CHAR(7),SUBSTITUTE(A1,"-",CHAR(7),LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))))))>96,
RIGHT(A1,LEN(A1)-SEARCH(CHAR(7),SUBSTITUTE(A1,"-",CHAR(7),LEN(A1)-
LEN(SUBSTITUTE(A1,"-",""))))),A1))

-- 
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Me" <Me@discussions.microsoft.com> skrev i en meddelelse
news:0C404BCD-B2CF-4616-87B4-F808C3DD687E@microsoft.com...
> Thanks, it's working now. Hard work with excel :)
>
> "Frank Kabel" wrote:
>
> > Hi
> > try
> > =IF(ISNA(LOOKUP(2,1/(MID(A1,seq,1)="-"),seq)),A1,IF(EXACT
> > (MID(A1,LOOKUP(2,1/(MID(A1,seq,1)="-"),seq)+1,1),UPPER(MID
> > (A1,LOOKUP(2,1/(MID(A1,seq,1)="-"),seq)+1,1))),A1,LEFT
> > (A1,LOOKUP(2,1/(MID(A1,seq,1)="-"),seq))))
> >
> > This solves the issue for examples 1 and 2.
> > Example 3 is according to your previous post NOT a valid
> > entry. So the formula would return
> > AA-a-SS
> >
> > as you said after the small case no upper case can occur
> >


0
8/3/2004 10:21:33 AM
Reply:

Similar Artilces: