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
427 Views

Similar Articles

[PageSpeed] 26

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:

Cells Formatted as Text
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I have a work sheet that contains over 10,000 rows and some columns of numbers contain numbers formatted as text. If I select the column and format as text it does not alter the text cells. I find I have to manually change each cell. <br> How can I search for a cell formatted as text? <br> or <br> How can I format the column to numbers? If the numbers are actually values [not zip codes or something similar]: Type the number 1 into a cell, Copy that cell, Select the column of figures,...

Converting Excel 94 to new format
I have an old copy of Office. Doesn't even say Office 95 or anything, just Office. I have one simple thing I wish to do with it, then will probably never use it again, so I don't want to go buy a newer version. I need to open and manipulate a file, but it keeps saying "file format is invalid". It saves it's files in .xls, which is the same file type I'm trying to open. I've gone to Microsoft to see if there would be any kind of update I could download for it, but can't find one. Anyone know if there is anything I can do to be able to use this file? ...

Text in Message Box
When we send an e-mail in Outlook 2003, only a portion of the message in the text box appears for the recipeint and in the sent item message. Any ideas? >-----Original Message----- >When we send an e-mail in Outlook 2003, only a portion of >the message in the text box appears for the recipeint and >in the sent item message. Any ideas? >. >One thing to check is the IE updates. ...

Controlling text fields in access 2002
I'm having some problems making my data input form nicely usable. First of all I'd like the text field to grow bigger when written on it, instead of scrolling as it does now. Is it possible to make the active field grow on top the others and then go back to it's defined size when another field is activated? I have a lot of fields in my form and I can't enlarge them anymore. And the fields might contain a lot of text or none at all. Another thing is that when printed, the totally empty fields could be left out completely. Now all I can do is minimize them and pri...

Excel Inserts Extra Rows In Text File
When I open a certain text file either with the macro listed below, o from windows explorer, Excel inserts a blank row between each row o data. Is there some setting that can be changed to correct this? Any assistance would be appreciated. Sincerely Jim Palmer ChDir "J:\" Dim Filter As String Dim Caption As String Filter = "All Files (*.*),*.*" Caption = "Select a File" SelectedFile = Application.GetOpenFilename(Filter, , Caption) Workbooks.OpenText Filename:=SelectedFile, _ Origin:=437, StartRow:=1 _ , DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, ...

How to print Selected Text of an E-mail msg in outlook 2003 ?
How to print Selected Text of an E-mail msg in outlook 2003 ? Say, I have a message which do contains my replies which I don't want to print, only what I print is the reply received from my customer, how can I ? Best Regards, Luqman copy and paste it in to word? or click forward and delete the text you don't want to print. If you use forward, you can choose to print just one page if you use word as your editor. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Need Help with C...

Date Format Mismatch
I need to accumulate data from several countries with different date-format than US. Like some send us the .txt files with following data: -------File1.txt----------------------------- Actual Data: 1/1/2004 Account Open Translation: 1/Jan/2004 Actual Data: 8/12/2004 Account Closed Translation: 8/Dec/2004 -------------------------------------------- -------File2.txt----------------------------- Actual Data: 1/1/2004 Account Open Translation: 1/Jan/2004 Actual Data: 8/12/2004 Account Closed Translation: 12/Aug/2004 * as per their regional date settings Ac...

Cell format changes General into Text after focus + Enter
Strange cell behavior. I have a simple sheet. One cell has a reference to another cell, made by Both cells formatted as General Cell A4 value 8 Cell A10: Function button F2: =A4 result in 8 OK so far. But when I click on A10 followed by ENTER, it changes into =A4 and is formatted as Text, not General ********* No Macros / additional VBA code / Excel 2003 on XP SP2 Bart ...

Conditional formatting and percentages
Hi I have some figures in a spreadsheet that a saved as the percentage format. If want have traffic lights colouration for these and if i wish to have any of the percentages that are between 0% and 2.5% percent to be (green) coloured. But because these figures are percentages I have to insert values between 0 and 0.025 which seems a little silly to me? Is there not an easier way of doing this as it looks as through errors could easily be made like this? The other 2 traffic lights are between 2.5% and 10 is (orange) and anything greater than 10% is (red). Please help me...

Text Baloons
I know that with other web design software you can add text or "word" baloons that pop-p when you highlight the text - Can this be done in Publisher? ...

Excel should allow me to change default number formats
I get irritated with some of the default settings within excel. I see that you can change the default setting for fonts, but not for number formats. Is there anyway there something that I'm missing with respect to this? ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message p...

Formating 2005345 to only 34!
Hello I got this date 2005345 which means year 2005, week 34, day 5 I want excel to format these values 2005345 and only show W34 How is this possible? /Anders -- a94andwi ------------------------------------------------------------------------ a94andwi's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21077 View this thread: http://www.excelforum.com/showthread.php?threadid=401138 With data in A1 try ="W"&MID(A1,5,2) -- Regards Roger Govier "a94andwi" <a94andwi.1uoqyc_1125587105.3224@excelforum-nospam.com> wrote in message...

outlook formatting
I have a user that lost her regular font in outlook. everything comes up bold or italic when typing messages. if i go into options it doesn't appear to be there as an option. anyone have any ideas? -- Please provide the following information: 1) Version of Outlook 2) E-mail environment (POP3, Exchange, etc.) 3) Any error messages, numbers (including from the event viewer, provide the event ID and source) 4) Any troubleshooting you've already done so it's not recommended again Thanks! -- Kathleen Orland Outlook Tips: http://www.outlook-tips.net/ http://www.howto-outlook....

Help with IF statement formatting!
Experts- I need a bit of help with a formula on a sales commission spreadsheet. I pay our salesman an additional bonus when they sell equipment at mor than 40% margin. Any $ amount above 40% margin is split between th company and the salesman. A typical sale example: Cost of Equipment - $3000 40% Margin Pricing - $5000 Actual Price Sold - $5500 Actual Sale Margin - 45% So, in this case, the salesman received his commission on the firs $5000 (40% margin pricing), then splits the remaining $500 (from sal price $5500 - 40% Margin Pricing of $5000). Company gets half ($250) salesman gets hal...

Export Publisher logo & text to .jpg file for thumbnail image
Need to download my Publisher logo & text to .jpg file with a physical size of 320 x 120 pixels, which I can then download into a thumbnail image on a busines website. OR is there another way to download my logo & text from Publisher into a .jpg of 320x120 pixels so I can download it into the website? You could open your saved .jpg in a digital image editing program and resize it. The freeware IrfanView may be useful to you... Download IrfanView 3.97 http://www.tucows.com/preview/194967.html (get the plug-ins too) Open the image in Irfanview and go to... Image / Resize-Resample...

How can I save an excel chart in gif format?
I want to create charts in excel and the display them on a web site. How can I save an excel chart in gif format? Than ks, Dom Andy Pope has a free add-in that you can use to export images or charts: http://www.andypope.info/vba/gex.htm Dom wrote: > I want to create charts in excel and the display them on a web site. How can > I save an excel chart in gif format? > > Than ks, Dom -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html try Sub ExportChartGIF() ActiveChart.Export Filename:="C:\a\MyChart.gif", _ FilterNam...

css: knowing how much room to leave for text?
When creating css layouts, how would you determine how much room to leave for text? Is there general rules on it? On May 1, 6:42=A0pm, "Andy B." <a_bo...@sbcglobal.net> wrote: > When creating css layouts, how would you determine how much room to leave > for text? Is there general rules on it? There are two kind of layouts, fixed and floating. Set width not in a fixed pixels but in percentages of the browser size and you will get dynamic width according amount of text your page would have. ...

Number/text formats
Hello, I need some help to get a Number format in Excelsheets. I work with artikel numbers in the format 00.0000/0 When I try to get this format in the user formats doesn't this work I become I this format working, example: I type 201001 -> has be comes to 20.1001/0 thanks -- falcon1966 ------------------------------------------------------------------------ falcon1966's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=34000 View this thread: http://www.excelforum.com/showthread.php?threadid=537628 In XL 97 with a Custom format of 00."."000...

How do I overide plain text when responding?
Hi all, I run outlook 2002. I notice that when I receive a plain text message my reply fond and "mark my comments with" options don't work. Is there some way to automatically overide this so. BTW, I have already set in the options that I want to compose in html as my default. I am a bit baffled as to why this doens't work on plain text messages I receive. Replies always use the format in which they were received, as well as the account through which it was received. If the sender is using plain text, please respect the sender's wishes and use plain text. Many...

Need to extract XML or SGML entities from a Unicode text
I'm working on a list of japaneese entities that contain the entity, the unicode hexadecimal code and the xml/sgml entity used for that entity. A unicode document is read into the program, then the program sorts out every doublet and the hexadecimal unicode code is extracted, but I dont know a way to find the xml or sgml-entity equivalent to the unicode code. Anyone who could give me a pointer? Best regards Frantic wrote: > I'm working on a list of japaneese entities that contain the entity, > the unicode hexadecimal code and the xml/sgml entity used for that > entity. ...

second() format
Is there a way to enter 3.5 seconds and display decmial point in the second field? Use a custom format like: hh:mm:ss.00 ???? Frankie wrote: > > Is there a way to enter 3.5 seconds and display decmial > point in the second field? -- Dave Peterson ec35720@msn.com ...

Simple Format for Customer Statement
Hello All Is there available another format for printing customer statements? I'm mostly interested in a format that shows charges and payments without all the details and allocation to invoices. In other words I need a statement is the old fashion simple (Debit, Credit, Balance) format. Thank you Thanks Morris, I'll try it out. "Morris Paint" wrote: > This is our current statement. We are having another one written that shows > current period transactions in one area and all open invoices in another. > > "Mario" <Mario@discussions.microsoft...

Text emails do not format
All of my incoming emails in Outlook 2003 are coming in as one long line of text. I've gone into my email options and turned off "Remove extra line breaks from text emails" but the problem still occurs. Does anyone know how to prevent my incoming text emails from turning the entire message into one long paragraph? Thanks! Fury Maybe the issue is with the person (or people) sending it. As a test, log into your webmail account, send yourself and email and then go into Outlook 2003 and see what it does... >-----Original Message----- >All of my incoming emails in ...

Work around Conditional Formatting
Dear all, There are 2 worksheets (Sheet1, Sheet2) containing similar data in my workbook. I want to underline the text in a cell in Sheet2 if the text in that cell is the same as the cell in the same place in Sheet1. (For example, if Sheet1!A1 = Sheet2!A1, then Sheet2!A1 is underlined.) Can I write a user-defined function to check whether the contents in two cells are equal? Thanks in advance. Best Regards, Andy Chips has lots of stuff on duplicates, see www.cpearson.com -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Andy Chan" <chankhan...

In Excell-2000 where do I set format for date?
In Excell-2000 where do I set format for date? In some tables I have it in dd/mm/yy, but in the others mm/dd/yy How do I change mm/dd/yy to dd/mm/yy? On my w2k Pro, Control Paned date format is set to dd/mm/yy yout it does not seem to affect Excel Hi If you select the cells with the dates in mm/dd/yy and go Format>Cells and in the dialog that opens, go for the Number tab, you can select the Date formats and choose whichever one suits or select Custom and type dd/mm/yy into the Type box. This will change the formatting of your dates. Hope this helps! Richard On 28 Jan, 16:34, &q...