FIND the occurrence of a Specific charecter from the Right

[Shamsuddeen]

Hi all,

I have designed a format in Sheet1 for printing Bank Telex Transfer Payment Request. I am using Cell D10 and D11 for putting the Beneficiary Name.

The beneficiary name is picked up from Cell A1. If the length of Name is greater than 35 char., then the first 35 char is displayed in D10 and the remaining char is shown in D11 as follows:

Name : The ABC Automobile Spare Parts Company Limited : Length = 46

Formula in D10 : =Left(A1,35) = The ABC Automobile Spare Parts Comp

Formula in D11 : = Mid(A1,36,len(A1)) = any Limited

What I would like have is that if the length is greater than 35 and the 35th char. is not a space (“p” in the above example) then the name should be cut at the first occurrence of space from the right side,. i.e, D10 should show “The ABC Automobile Spare Parts” not “The ABC Automobile Spare Parts Comp” and D11 should show “Company Limited”

I know that above can be achieved by using macro. But is it possible to do without a macro. If yes, how ? . The main concern is to find the first occurrence of a specific character from the right side using FIND Command.

Regards,

Shamsuddeen

 

[PaddyD]

Hi - replace the "@" in the formula with a character / combination of characters that you know won't turn up in your data.

Problem:

For a string of arbitrary lenght, return only the whole words before the 35th position.

Solution:

=MID(A1,1,FIND("@",SUBSTITUTE(A1," ","@",35-LEN(SUBSTITUTE(MID(A1,1,35)," ",""))),1))


See the example:

Book6
	A	B	C	D
1	TheABCAutomobileSparePartsCompanyLimited
2	Current
3	TheABCAutomobileSparePartsComp
4	anyLimited
5	Revised
6	TheABCAutomobileSpareParts
7	CompanyLimited
8
Sheet2

You will need to embed this within a couple of ifs that check whether the source string is > 35 characters (otherwise you'll get #value errors).

=IF(LEN(A1)>35,MID(A1,1,FIND("@",SUBSTITUTE(A1," ","@",35-LEN(SUBSTITUTE(MID(A1,1,35)," ",""))),1)),A1)

=IF(LEN(A1)>35,RIGHT(A1,LEN(A1)-LEN(A6)),"")


Paddy
This message was edited by PaddyD on 2002-09-11 15:01

 

[Juan Pablo González]

Nice one Paddy. I was using a more "expensive" formula, altough shorter !

I would only change the 35 in there for a 36, like this

=MID(A6,1,FIND("@",SUBSTITUTE(A6," ","@",36-LEN(SUBSTITUTE(MID(A6,1,36)," ",""))),1))

Because of this string

This is one example string that has 35 characters in the first part

with your formula, the text gets cut at the "that", when it should cut at the "has".

My version is this, just in case... uses the MoreFunc.xll

{=LEFT(A2,MAX(SETV(INTVECTOR(36,1))*(CODE(MID(A2,GETV(),1))=32)))}

 

[PaddyD]

Slightly makes up for my divide by 5 issues yesterday

Good point re 36!

Paddy

 

[Shamsuddeen]

Hi, PaddyD and Juan Pablo G

It is great and wonderful.

Could you please make me clear using the "@"


Thank you very much

Shamsuddeen

 

[PaddyD]

=MID(A6,1,FIND("@",SUBSTITUTE(A6," ","@",36-LEN(SUBSTITUTE(MID(A6,1,36)," ",""))),1))

36-LEN(SUBSTITUTE(MID(A6,1,36)," ","")))
- take away the l;ength of the string when spaces have been substitued for 'nothings' to determine how many spaces (words) there are.

SUBSTITUTE(A6," ","@",36-LEN(SUBSTITUTE(MID(A6,1,36)," ",""))),1))
- substitute the x'th space with a @, where the value of x is determined by the calculation above. This gives you a 'placeholder' that marks the beginning of the word in question

FIND("@",SUBSTITUTE(A6," ","@",36-LEN(SUBSTITUTE(MID(A6,1,36)," ",""))),1))
- find the position of this placeholder

=MID(A6,1,FIND("@",SUBSTITUTE(A6," ","@",36-LEN(SUBSTITUTE(MID(A6,1,36)," ",""))),1))
- use the position in a mid() argument to return the desired string.

It is important that whatever you are using as a 'placeholder' doesn't turn up in your source data, otherwise the find() bit will give erroneous results. Pick something else if your strings might legitimately contain @'s. (a long string of z's would do just as well)

Paddy

 

[Shamsuddeen]

Hi, PaddyD

The logic is great!!!!

Thank you very much.


Regards,

Shamsuddeen