Shamsuddeen
Active Member
- Joined
- Feb 16, 2002
- Messages
- 292
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
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