# FIND the occurrence of a Specific charecter from the Right

#### Shamsuddeen

##### Active Member
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

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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
ABCD
1TheABCAutomobileSparePartsCompanyLimited
2Current
3TheABCAutomobileSparePartsComp
4anyLimited
5Revised
6TheABCAutomobileSpareParts
7CompanyLimited
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)),"")

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

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

Slightly makes up for my divide by 5 issues yesterday

Good point re 36!

Hi, PaddyD and Juan Pablo G

It is great and wonderful.

Could you please make me clear using the "@"

Thank you very much

Shamsuddeen

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

The logic is great!!!!

Thank you very much.

Regards,

Shamsuddeen

Replies
2
Views
9K
Replies
32
Views
13K

1,219,995
Messages
6,151,359
Members
451,022
Latest member
Baijano23

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back