FIND the occurrence of a Specific charecter from the Right

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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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)),"")


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

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
Slightly makes up for my divide by 5 issues yesterday :)

Good point re 36!

Paddy
 

Shamsuddeen

Active Member
Joined
Feb 16, 2002
Messages
292

ADVERTISEMENT

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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
=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
 

Forum statistics

Threads
1,144,274
Messages
5,723,440
Members
422,497
Latest member
dougy99

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top