Text format

M

Me

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 ?
 
M

Me

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 ?
 
F

Frank Kabel

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
Click to expand...
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
Click to expand...
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"
 
M

Me

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.
 
F

Frank Kabel

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
Click to expand...
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.
 
S

Soo Cheon Jheong

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
_ _
^¢¯^
--
 
M

Me

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

Frank Kabel

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
Click to expand...
looooong formula for this cases:
 
L

Leo Heuser

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