CODEWITHSUNDEEP
excelexcel-formula
tim r
tim r

Reputation: 89

Remove everything after the fourth instance of a character

So I have a bulleted list in a cell and I want to only include the first 3 bullets in that cell.

As an example, I know I can use =LEFT(P6,(FIND(CHAR(149),P6)+400)) to get the first 400 characters (the template I'm building this for can only handle 400 character max) but I want to stop just before the 4th bullet so that things are not cut off.

Is there a formula to use that only shows everything to the left of the 4th bullet. Its that nth number that I'm struggling with.

Upvotes: 1

Views: 392

Answers (1)

tigeravatar
tigeravatar

Reputation: 26660

Give this a try:

=TRIM(LEFT(SUBSTITUTE(A1,CHAR(149),REPT(" ",LEN(A1)),4),LEN(A1)))

Upvotes: 4

Related Questions