excel: counting characters between symbol

[kawnz]

I am compiling a list of "canned messages" that will be entered in our billing system where the employees will be able to enter a code, that will write the whole message for them. e.g. "RFREC" = "Received refund request from"

At first I thought I was limited to 30 characters for the message, so I put in conditional formatting to count the number of characters in the cell and shade the cell yellow if it exceeded 30 characters.

I then found out that I am limited to 30 characters per line, but not limited in the number of lines per message. I now want to put a | symbol to signify the end of the line, and I would like excel to count the number of characters between those | symbols. That way I know where to end one line and start the next.

eg. Refund approved by ___ | forwarding spreadsheet to ___ | to cut refund check

Is there a way to do that, and if so, could you please provide the formula for this function?

Thanks ever so much for all the help you have given me thus far.

 

[SkipVought]

Hi,

What good are VERTICAL BARS going to do when you need LINE FEEDS?

Skip,

[red]Be advised:[/red] Alcohol and Calculus do not mix!
If you drink, don't derive!

Want to get great answers to your Tek-Tips questions? Have a look at FAQ222-2244

 

[kawnz]

this is for data prep work - so we know how long the lines will be. It will all be manually entered.

if you have another suggestion on making this work, I am all ears. (or eyes in this case)

 

[SkipVought]

A formula for the number of lines would be
[tt]
=INT((LEN(A1)-1)/30)+1
[/tt]


Skip,

[red]Be advised:[/red] Alcohol and Calculus do not mix!
If you drink, don't derive!

Want to get great answers to your Tek-Tips questions? Have a look at FAQ222-2244